前端时间写了一个按月统计的报表,首先用了最笨的办法,一个个单元格值计算,后来试了一下sqlserver自带的两个函数,比原来要简单一些,分享一下语句如下:
select * from (
select sname,smonth+colname as colname,colvalue from
(
select 'a' as sname,'jan' as smonth,123 as iqu,234 as imoney
union
select 'b' as sname,'jan' as smonth,234 as iqu,543 as imoney
union
select 'c' as sname,'feb' as smonth,645 as iqu,754 as imoney
union
select 'c' as sname,'may' as smonth,645 as iqu,754 as imoney
) as m
unpivot -----列转行
(
colvalue for colname in(iqu,imoney)
) as n
) as x
pivot-------行转列
(
max(colvalue) for colname in([janiqu],[janimoney],[febiqu],[febimoney],[mayiqu],[mayimoney])
) as y