By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

how to select the colums value as Column header?

P: 11
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
Dec 31 '09 #1
Share this Question
Share on Google+
8 Replies


code green
Expert 100+
P: 1,726
Column headings of what?
Dec 31 '09 #2

P: 11
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
Jan 2 '10 #3

nbiswas
100+
P: 149
Use Pivot. It is very simple
Jan 4 '10 #4

P: 11
sorry sir, i am not familiar with Pivot please help me to sort out
Jan 4 '10 #5

nbiswas
100+
P: 149
Try this

Sample data

Expand|Select|Wrap|Line Numbers
  1. declare @t table(id int, CertificateName varchar(50))
  2. insert into @t 
  3.     select 1 ,'Secondary' union all select 2,'Highersecondary' union all
  4.     select 3 ,'Undergraduate' union all select 4,'PostGraduate' 
Query:

Expand|Select|Wrap|Line Numbers
  1. select         
  2.         [Secondary]
  3.         ,[Highersecondary]
  4.         ,[Undergraduate]
  5.         ,[PostGraduate]
  6.     from (select id,CertificateName from @t)as X
  7.     pivot
  8.     (min(id) for CertificateName IN
  9.     ([Secondary],[Highersecondary],[Undergraduate],[PostGraduate]))as Pvt
Output:

Expand|Select|Wrap|Line Numbers
  1. Secondary    Highersecondary    Undergraduate    PostGraduate
  2. 1    2    3    4
Jan 4 '10 #6

P: 11
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
Jan 4 '10 #7

nbiswas
100+
P: 149
Try dynamic pivoting

Sample data

Expand|Select|Wrap|Line Numbers
  1. create  table #t(id int, CertificateName varchar(50))
  2. insert into #t 
  3.     select 1 ,'Secondary' union all select 2,'Highersecondary' union all
  4.     select 3 ,'Undergraduate' union all select 4,'PostGraduate' 
--Program starts
Expand|Select|Wrap|Line Numbers
  1. declare @col_list varchar(max)
  2. declare @dynquery varchar(max)
  3. select 
  4. distinct   IsNull(@col_list,'') + 
  5. ',[' + cast(CertificateName as varchar(50)) + ']' as list 
  6. into #temp from #t
  7. select @col_list = stuff(namelist,1,1,'') from(select cast(list as varchar(max)) from #temp for xml path (''))X(namelist)
  8.  
  9. set @dynquery = 'select * from #t pivot ( min(id) for CertificateName in (' + @col_list + ') ' + ') as pvt'
  10. exec (@dynquery)
  11.  
  12. drop table #temp
  13. drop table #t
Output:
Expand|Select|Wrap|Line Numbers
  1. Highersecondary    PostGraduate    Secondary    Undergraduate
  2. 2    4    1    3
  3.  
Jan 4 '10 #8

P: 1
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. create  table #t(id int, CertificateName varchar(50))
  2. insert into #t 
  3.     select 1 ,'Seco&ndary' union all select 2,'<Highersecondary>' union all
  4.     select 3 ,'Undergraduate' union all select 4,'PostGraduate' 
Output:
Expand|Select|Wrap|Line Numbers
  1. &lt;Highersecondary&gt;    PostGraduate    Seco&amp;ndary    Undergraduate
  2. NULL    4    NULL    3
  3.  
Please help!

@nbiswas
Dec 12 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.