SQL中纵横表转换

2种⽅法实现了静态与动态的纵模表的转换的实例。⽅法1：

select name

,sum(case subject when'数学' then source else 0 end ) as '数学',sum(case subject when'英语' then source else 0 end ) as '英语',sum(case subject when'语⽂' then source else 0 end ) as '语⽂'from test

group by name

⽅法2：

declare @sql varchar(8000)set @sql='select name,'

select @sql =@sql +'sum(case subject when '''+subject+''' then source else 0 end ) as '''+subject+''','from (select distinct subject from test) as a

select @sql=left(@sql,len(@sql)-1)+' from test group by name'exec(@sql)go

PS:如果遇到⽇期汇总可⽤max()来解决如下:

declare @sql varchar(8000)

set @sql='select name,convert(varchar(10),date_1,120),'

select @sql =@sql +'max(case col_1 when '''+col_1+''' then date_1 else 0 end) as '''+col_1+''','from (select distinct col_1 from Table_1) as a

select @sql=left(@sql,len(@sql)-1)+' from Table_1 group by name,convert(varchar(10),date_1,120) 'exec(@sql)