求一汇总语句谢谢!

[klyh1207]

有数据 类似:
id name price class

1 k 12 1
2 f 13 1
3 e 14 1
4 q 12 2
5 w 13 2
6 r 15 2
7 h 12 3
8 j 13 3
9 k 16 3

.....
....
...

class 为类别标识

类别不一定多少

最后结果想只出来一条结果

如:

class_sum class_1 class_2 class_3 .........

120 39 40 41


请各位帮帮忙吧!

[liangCK]

declare @s varchar(8000);
set @s = '';

select @s = @s + ',sum(case when class='+rtrim(class)+' then price else 0 end) as class_'+rtrim(class)
from tb group by class;

exec('select sum(price)'+@s+' from tb')

[dawugui]

declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case class when ''' + cast(class as varchar) + ''' then price else 0 end) [class_' + cast(class as varchar) + ']'
from (select distinct class from tb) as a
set @sql = 'select sum(price) class_sum ' + @sql + ' from tb'
exec(@sql) 

[dawugui]

create table tb(id int,name varchar(10),price int,class int)
insert into tb values(1 ,'k', 12 ,1)
insert into tb values(2 ,'f', 13 ,1)
insert into tb values(3 ,'e', 14 ,1)
insert into tb values(4 ,'q', 12 ,2)
insert into tb values(5 ,'w', 13 ,2)
insert into tb values(6 ,'r', 15 ,2)
insert into tb values(7 ,'h', 12 ,3)
insert into tb values(8 ,'j', 13 ,3)
insert into tb values(9 ,'k', 16 ,3)
go

declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',sum(case class when ''' + cast(class as varchar) + ''' then price else 0 end) [class_' + cast(class as varchar) + ']'
from (select distinct class from tb) as a
set @sql = 'select sum(price) class_sum ' + @sql + ' from tb'
exec(@sql) 

drop table tb

/*
class_sum   class_1     class_2     class_3     
----------- ----------- ----------- ----------- 
120         39          40          41
*/

[-狙击手-]

龟V5

[「已注销」]

好多五星

[klyh1207]

3KS 各位 没想到一句话引出这么多大牛!