declare @sql varchar(8000)
set @sql = 'select ID'
select @sql = @sql + ','''+name+''' name,sum(case name when '''+name+''' then T1 end) T1,sum(case name when '''+name+''' then T2 end) t2'
from (select distinct name from t_mytab) as a
select @sql = @sql+' from t_mytab group by ID'exec(@sql)
set @sql = 'select ID'
select @sql = @sql + ','''+name+''' name,sum(case name when '''+name+''' then T1 end) T1,sum(case name when '''+name+''' then T2 end) t2'
from (select distinct name from t_mytab) as a
select @sql = @sql+' from t_mytab group by ID'exec(@sql)
insert #t_mytab values('A', 1 , 11, 12)
insert #t_mytab values('A', 2 , 12 , 13)
insert #t_mytab values('A', 3, 13 , 14)
insert #t_mytab values('B', 1 ,10 , 11)
insert #t_mytab values('B', 2 , 12 , 14)
insert #t_mytab values('C', 1 , 11 , 12)
insert #t_mytab values('C', 2 , 10 , 12)
insert #t_mytab values('C', 3, 12 ,14)declare @sql varchar(8000)
set @sql = 'select ID'
select @sql = @sql + ','''+name+''' name,sum(case name when '''+name+''' then T1 end) T1,sum(case name when '''+name+''' then T2 end) t2'
from (select distinct name from #t_mytab) as a
select @sql = @sql+' from #t_mytab group by ID'exec(@sql)
go
drop table #t_mytab
/*
ID name T1 t2 name T1 t2 name T1 t2
----------- ---- ----------- ----------- ---- ----------- ----------- ---- ----------- -----------
1 A 11 12 B 10 11 C 11 12
2 A 12 13 B 12 14 C 10 12
3 A 13 14 B NULL NULL C 12 14
*/
insert #t_mytab values('A', 1 , 11, 12)
insert #t_mytab values('A', 2 , 12 , 13)
insert #t_mytab values('A', 3, 13 , 14)
insert #t_mytab values('B', 1 ,10 , 11)
insert #t_mytab values('B', 2 , 12 , 14)
insert #t_mytab values('C', 1 , 11 , 12)
insert #t_mytab values('C', 2 , 10 , 12)
insert #t_mytab values('C', 3, 12 ,14)declare @sql varchar(8000),@a int
select @sql = 'select ID',@a=0
select @a=@a+1,@sql = @sql + ','''+name+''' name'+cast(@a as varchar(10))+',sum(case name when '''+name+''' then T1 end) T1_'+cast(@a as varchar(10))+',sum(case name when '''+name+''' then T2 end) t2_'+cast(@a as varchar(10))
from (select distinct name from #t_mytab) as a
select @sql = @sql+' from #t_mytab group by ID'exec(@sql)
go
drop table #t_mytab
----------- ----- ----------- ----------- ----- ----------- ----------- ----- ----------- -----------
1 A 11 12 B 10 11 C 11 12
2 A 12 13 B 12 14 C 10 12
3 A 13 14 B NULL NULL C 12 14
set @s=''select @s=@s+',[name('+name+')]='''+name
+''',[T1('+name+')]=sum(case name when '''+name+''' then t1 end)'
+',[T2('+name+')]=sum(case name when '''+name+''' then t2 end)'
from(select distinct name from t_mytab) aexec('select id'+@s+' from t_mytab group by id')
declare @s1 varchar(8000),@s2 varchar(8000)
select @s1='',@s2=''select @s1='(select ID,name,T1,T2 from #t_mytab where name='''+min(name)+''')a'
from t_mytabselect @s2=@s2+' full join(select ID,name,T1,T2 from t_mytab where name='''+name
+''')'+name+' on a.id='+name+'.id'
from(select distinct name from t_mytab where name<>(select min(name) from t_mytab)) aexec('select * from'+@s1+@s2)
create table #t_mytab(name varchar(2),ID int,T1 int,T2 int)
insert into #t_mytab
select 'A',1,11,12
union all select 'A',2,12,13
union all select 'A',3,13,14
union all select 'B',1,10,11
union all select 'B',2,12,14
union all select 'C',1,11,12
union all select 'C',2,10,12
union all select 'C',3,12,14
--数据处理
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''select @s1='(select ID,name,T1,T2 from #t_mytab where name='''+min(name)+''')a'
from #t_mytabselect @s3=@s3+','+name+'.name,'+name+'.T1,'+name+'.T2'
,@s2=@s2+' full join(select ID,name,T1,T2 from #t_mytab where name='''+name
+''')'+name+' on a.id='+name+'.id'
from(select distinct name from #t_mytab where name<>(select min(name) from #t_mytab)) aexec('select a.*'+@s3+' from'+@s1+@s2)
go--删除测试环境
drop table #t_mytab
---- ---- ------- ------- ---- ------- ----------- ---- ----------- -----------
1 A 11 12 B 10 11 C 11 12
2 A 12 13 B 12 14 C 10 12
3 A 13 14 NULL NULL NULL C 12 14