SQL行转列问（多列）

[terry三]

行：
ID 组 S1 S2 S3 S4 S5
3992 AA 250 252 0 0 250
3993 AA 100 100 0 0 100
3994 AA 120 125 45 0 120
3989 BB 500 504 0 500 0
3990 BB 250 250 0 0 250
3991 BB 100 100 0 0 100
3992 BB 250 2463 0 0 250
3989 CC 405 311 0 0 405
3990 CC 250 55 0 0 250
3991 CC 320 446 0 0 320
3992 CC 250 346 0 0 250

转成列


求SQL

[hhhttt_]

create table tb(id int,[group] nvarchar(10),S1 int,S2 int,S3 int,S4 int ,S5 int) insert into tb select 3992, 'AA' ,250 ,252, 0, 0, 250 union all select 3993 ,'AA', 100 ,100, 0 ,0 ,100 union all select 3994 ,'AA' ,120, 125 ,45, 0 ,120 union all select 3989, 'BB', 500, 504 ,0 ,500 ,0 union all select 3990 ,'BB', 250 ,250 ,0 ,0 ,250 union all select 3991, 'BB', 100, 100, 0 ,0, 100 union all select 3992, 'BB', 250, 2463, 0, 0 ,250 union all select 3989, 'CC' ,405 ,311, 0 ,0, 405 union all select 3990 ,'CC' ,250, 55 ,0 ,0, 250 union all select 3991, 'CC' ,320 ,446 ,0 ,0 ,320 union all select 3992 ,'CC' ,250, 346 ,0 ,0 ,250 declare @sql varchar(max) set @sql='' select @sql=@sql+',組='''+[group]+''' ,SUM(case when [group]='''+[group]+''' then S1 end) as '+[group]+'S1 ,SUM(case when [group]='''+[group]+''' then S2 end) as '+[group]+'S2 ,SUM(case when [group]='''+[group]+''' then S3 end) as '+[group]+'S3 ,SUM(case when [group]='''+[group]+''' then S4 end) as '+[group]+'S4' from (select distinct [group],s='S1' from tb)as t set @sql='select id'+@sql +' from tb group by id' --print @sql exec(@sql)

[terry三]

多谢。组不限定，不知可不可以。