create table ta(ID int, ClassType varchar(2),[SUM] int)
insert ta select 1, 'A', 4
union all select 1, 'A', 1
union all select 1, 'B', 2
union all select 1, 'B', 1
union all select 1, 'C', 5
union all select 6, 'A', 2
union all select 6, 'A', 1
union all select 6, 'B', 4
union all select 6, 'B', 3
union all select 6, 'C', 1
union all select 6, 'C', 2
declare @s varchar(4000)
set @s=''
select @s=@s+','+quotename(ClassType)+'=sum(case ClassType when '+quotename(ClassType,'''')
+' then [sum] else 0 end)'
from ta group by ClassType
set @s='select id'+@s+' from ta group by id'
exec(@s)id A B C
----------- ----------- ----------- -----------
1 5 3 5
6 3 7 3(2 行受影响)
insert ta select 1, 'A', 4
union all select 1, 'A', 1
union all select 1, 'B', 2
union all select 1, 'B', 1
union all select 1, 'C', 5
union all select 6, 'A', 2
union all select 6, 'A', 1
union all select 6, 'B', 4
union all select 6, 'B', 3
union all select 6, 'C', 1
union all select 6, 'C', 2
declare @s varchar(4000)
set @s=''
select @s=@s+','+quotename(ClassType)+'=sum(case ClassType when '+quotename(ClassType,'''')
+' then [sum] else 0 end)'
from ta group by ClassType
set @s='select id'+@s+' from ta group by id'
exec(@s)id A B C
----------- ----------- ----------- -----------
1 5 3 5
6 3 7 3(2 行受影响)
有没有可以不用临时表的?
------如果你的ClassType固定只有這麼三種,可以用如下語句。
否則就必要用roy_88(中国风_燃烧你的激情!!!) 的方法,用動態SQL語句。Select
ID,
SUM(Case ClassType When 'A' Then [SUM] Else 0 End) As A,
SUM(Case ClassType When 'B' Then [SUM] Else 0 End) As B,
SUM(Case ClassType When 'C' Then [SUM] Else 0 End) As C
From
表
Group By
ID
但如果ClassType不止A,B,C的话就要改查询语句,roy_88的不用改.
drop table ta
go
create table ta(ID int, ClassType varchar(2),[SUM] int)
insert ta select 1, 'A', 4
union all select 1, 'A', 1
union all select 1, 'B', 2
union all select 1, 'B', 1
union all select 1, 'C', 5
union all select 6, 'A', 2
union all select 6, 'A', 1
union all select 6, 'B', 4
union all select 6, 'B', 3
union all select 6, 'C', 1
union all select 6, 'C', 2select ID,
sum(case when ClassType = 'A' then [SUM] else 0 end) as 'A',
sum(case when ClassType = 'B' then [SUM] else 0 end) as 'B',
sum(case when ClassType = 'C' then [SUM] else 0 end) as 'C'
from ta
group by ID