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

how we can display two columns according to the same rows in each column rowwise

P: 4
our database is

district field
highland rookies
volcano peewees
volcano rookies
noida mighty
noida peewees
ghaz rookies
ghaz freshman


now i want my output like this:

district rookies peewees mighty freshman
highland 1 null null null
volcano 1 1 null null
noida null 1 1 null
ghaz 1 null null 1
Feb 12 '10 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Check PIVOT/UNPIVOT in BOL.

Happy Coding !!!

~~ CK
Feb 12 '10 #2

nbiswas
100+
P: 149
Try something like this

create table #t (district varchar(50),field varchar(50))
insert into #t
select 'highland','rookies' union all select 'volcano','peewees' union all
select 'volcano','rookies' union all select 'noida','mighty' union all
select 'noida','peewees' union all select 'ghaz','rookies' union all
select 'ghaz','freshman'
--select * from #t
--Program starts
declare @col_list varchar(max)
declare @dynquery varchar(max)
select distinct IsNull(@col_list,'') + ',[' + cast(field 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 district,' + @col_list + 'from #t pivot ( max(field) for field in (' + @col_list + ') ' + ') as pvt'
exec (@dynquery)
--Program ends
drop table #temp
drop table #t
Feb 15 '10 #3

Post your reply

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