| Code | name | Value | |
| 1 | name1 | 20 | |
| 2 | name1 | 30 | |
| 1 | name2 | 30 | |
| 2 | name2 | 30 | |
| 3 | name3 | 20 | |
| 1 | name3 | 20 | |
| 2 | name3 | 30 | |
| Code | Name1 | Name2 | Name3 |
| 1 | 20 | 30 | 20 |
| 2 | 30 | 30 | 30 |
| 3 | 0 | 0 | 20 |
------------------------------------------------------------------------------------------
select code ,sum(case when name ='name1' then Value else 0 end )as name1,
sum(case when name ='name2' then Value else 0 end )as name2,
sum(case when name ='name3' then Value else 0 end )as name3
from tb1 group by code
示例2
表:
id sid course result
1 2005001 语文 80.0
2 2005001 数学 90.0
3 2005001 英语 80.0
4 2005002 语文 56.0
5 2005002 数学 69.0
6 2005002 英语 89.0
执行
select sid,语文=isnull(sum(case course when '语文' then result end),0),
数学=isnull(sum(case course when '数学' then result end),0),
英语=isnull(sum(case course when '英语' then result end),0)
from result
group by sid
order by sid
得出结果
sid 语文 数学 英语
2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0
示例3
create table #abcd(Mid int, LossType varchar(50), LossTime numeric(18,2))
insert into #abcd
select 1,'A','0.1'
union all select 1,'B','0.2'
union all select 1,'A','0.3'
union all select 1,'C','0.1'
union all select 2,'B','0.1'
union all select 2,'B','0.2'
union all select 3,'A','0.2'
declare @sql varchar(2000)
set @sql =''
select @sql =@sql +','+lossType+' =sum(case LossType when '+QUOTENAME(LossType,'''')+' then LossTime else 0 end)'
''''''select @sql =@sql +',sum(case when LossType = '''+lossType+''' then LossTime else null end) as '+LossType+''
from
(select distinct LossType from #abcd) a
exec(
'select mid '+@sql+' from #abcd group by mid')
select * from #abcd
