列转行问题

[hipop]

表A
a1 a2 a3 ...
1 2 3 ...

表B
b1 b2
1 k
2 f
3 c
...

现在需要得到下面的表
字段名: 1 2 3 ...
一条记录:k f c ...

大家帮我看看,谢谢了!

[中国风]

create table ta(b1 int ,b2 varchar(5))
insert ta
select 1, 'k'union all
select 2, 'f'union all
select 3, 'c'

declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+rtrim(b1)+']=max(case b1 when '''+rtrim(b1)+''' then b2 end)'
from ta group by b1
set @sql=stuff(@sql,1,1,'')
set @sql='select '+@sql+' from ta'
exec(@sql)
1 2 3
----- ----- -----
k f c

警告: 聚合或其它 SET 操作消除了空值。

[yuedeem]

路过学习~

[hipop]

试试看 先~

[askyin]

up

[being21]

路过学习~

[askyin]

create table ta(b1 int ,b2 varchar(5))
insert ta
select 1, 'k'union all
select 2, 'f'union all
select 3, 'c'


declare @sql varchar(1024)
set @sql=''
select @sql=@sql+',max(case when b1='+cast(b1 as varchar(20))+' then b2 else '''' end) as '''+cast(b1 as varchar(20))+'''' from ta
set @sql='select'+stuff(@sql,1,1,' ')+' from ta'
exec(@sql)


k f c