求助:关于一个查询的SQL语句
我有一个表
ID ClassType SUM
1 A 4
1 A 1
1 B 2
1 B 1
1 C 5
6 A 2
6 A 1
6 B 4
6 B 3
6 C 1
6 C 2

想要得到的结果是
ID A B C
1 5 3 5
6 3 7 3

请高人指点如何实现,谢谢!

[解决办法]
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 行受影响)


[解决办法]
Soul25() ( ) 信誉：100 Blog 2007-04-02 09:56:14 得分: 0


有没有可以不用临时表的?


------

如果你的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
[解决办法]
借用下roy_88(中国风_燃烧你的激情！！！) 的数据.
但如果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 ', 2

select 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