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

[chaudharygaurav2]

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

[ck9663]

Check PIVOT/UNPIVOT in BOL.

Happy Coding !!!

~~ CK

[nbiswas]

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