How to generate a query where dynamic column name?

[nokomoli]

Hi guys,

i 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 | Abu
C | Kawan
A | Bayu
B | Cawam
C | didik

how i write a query like below result?

A | B | C
Ali Abu Kawan
Bayu cawam didik

Can anyone help?

[sknake]

Are you guaranteed to have the same number of A,B,C records? Also are there any other columns in that table? This table structure doesn't make much sense.

[fanzhouqi]

--> 测试数据: @T1
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

a     b     c     
----- ----- ----- 
Ali   Cawam Kawan
Bayu  Abu   didik

（所影响的行数为 2 行）

[mail2saion]

Can not understand your requirement + table design is not up to the mark. Also hard coding values may not help you.