How to generate a query where dynamic column name? – MS SQL

October 2, 2009 · Leave a Comment
Filed under: Featured, MS SQL Server 

am faced a problem here where is i need to select a query where the column name is dynamic:

table a

col1 | col2
—————-
A | Ali
B | Steven
C | Kawan
A | Bayu
B | Cawam
C | didik




how i write a query like below result?

declare @T1 table (col1 varchar(1),col2 varchar(5))
insert into @T1
select 'A','Ali' union all
select 'B','Abu' union all
select 'C','Kawan' union all
select 'A','Bayu' union all
select 'B','Cawam' union all
select 'C','didik'

if object_id('tempdb..#') is not null
	drop table #
select *
into #
from @T1 a
order by col1

alter table # add flag int
go
declare @i int
set @i = 0
declare @c varchar(10)
set @c = ''
update a set
	@i = case when @c = col1 then @i+1 else 1  end
	,flag = @i
	,@c = col1
from # a

select
	a= max(case when col1 = 'a' then col2 else '' end)
	,b = max(case when col1 = 'b' then col2 else '' end)
	,c =max( case when col1 = 'c' then col2 else '' end)
from #
group by flag