how to select the colums value as Column header?

[lucindaa]

For Example
I have Table name called "tbl_certificate"

Fields as follows

id Certificate name
1 Secondary
2 Highersecondary
3 Undergraduate

i want the output as follows

Secondary Highersecondary Undergraduate

Please anyone help me to solve this

[code green]

Column headings of what?

[lucindaa]

Hi code Green
Thanks for replying.
For me Column value should be displayed as field name
Table 1
Id Certifiacte Name
1 Secondary
2 HigherSecondary
3 UnderGraduate
4 PostGraduate

The Output Should be as follows
Secondary HigherSecondary UnderGraduate PostGraduate

[nbiswas]

Use Pivot. It is very simple

[lucindaa]

sorry sir, i am not familiar with Pivot please help me to sort out

[nbiswas]

Try this

Sample data

declare @t table(id int, CertificateName varchar(50))
insert into @t 
    select 1 ,'Secondary' union all select 2,'Highersecondary' union all
    select 3 ,'Undergraduate' union all select 4,'PostGraduate' 

Query:

select         
        [Secondary]
        ,[Highersecondary]
        ,[Undergraduate]
        ,[PostGraduate]
    from (select id,CertificateName from @t)as X
    pivot
    (min(id) for CertificateName IN
    ([Secondary],[Highersecondary],[Undergraduate],[PostGraduate]))as Pvt

Output:

Secondary    Highersecondary    Undergraduate    PostGraduate
1    2    3    4

[lucindaa]

it works well but the no of rows are dynamic
in that case how can i do it
i mean these are dynamic
"Secondary Highersecondary Undergraduate PostGraduate "
any value can be there instead of above and no of value can be dynamic

[nbiswas]

Try dynamic pivoting

Sample data

create  table #t(id int, CertificateName varchar(50))
insert into #t 
    select 1 ,'Secondary' union all select 2,'Highersecondary' union all
    select 3 ,'Undergraduate' union all select 4,'PostGraduate' 

--Program starts

declare @col_list varchar(max)
declare @dynquery varchar(max)
select 
distinct   IsNull(@col_list,'') + 
',[' + cast(CertificateName as varchar(50)) + ']' as list 
into #temp from #t
select @col_list = stuff(namelist,1,1,'') from(select cast(list as varchar(max)) from #temp for xml path (''))X(namelist)
 
set @dynquery = 'select * from #t pivot ( min(id) for CertificateName in (' + @col_list + ') ' + ') as pvt'
exec (@dynquery)
 
drop table #temp
drop table #t

Output:

Highersecondary    PostGraduate    Secondary    Undergraduate
2    4    1    3
 

[shilpz]

Hi,

I have special characters in my column value and I get null values.

create  table #t(id int, CertificateName varchar(50))
insert into #t 
    select 1 ,'Seco&ndary' union all select 2,'<Highersecondary>' union all
    select 3 ,'Undergraduate' union all select 4,'PostGraduate' 

Output:

<Highersecondary>    PostGraduate    Seco&ndary    Undergraduate
NULL    4    NULL    3
 

Please help!

@nbiswas