how to select the colums value as Column header?

[lucindaa]

For Example
I have Table name called "tbl_certificat e"

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]

Column value should be displayed as field name

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]

Solution to how to select the colums value as Column header

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]

no of rows are dynamic

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]

how to select the colums value as Column header?

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]

Special characters in the column values

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!

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