參考:Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
insert table1
select 1,20
union all select 1,30
union all select 1,50--select * from table1
SELECT id,
sum(CASE WHEN num = 20 THEN 20 ELSE 0 END) AS c1,
sum(CASE WHEN num = 30 THEN 30 ELSE 0 END) AS c2,
sum(CASE WHEN num = 50 THEN 50 ELSE 0 END) AS c3
FROM table1
GROUP BY iddrop table table1
sum(case when haha=3 then aaa end),
sum(case when haha=2 then aaa end )
from bbb
group by id
嘻嘻,何必呢,马可大虾的多好啊.马可的多通用啊
select '1' , '20' , '30' , '40'