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)