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 操作消除了空值。
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
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 操作消除了空值。
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