sqlserver 行列转换适用一定格式报表的统计

最新推荐文章于 2023-08-21 22:00:16 发布

原创最新推荐文章于 2023-08-21 22:00:16 发布 · 837 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver

数据库专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何使用SQLserver的UNPIVOT和PIVOT函数来简化按月统计数据报表的编写过程。通过示例代码，展示了从原始数据到最终的行列转换，大大减少了计算的复杂性，提高了查询效率。

前端时间写了一个按月统计的报表，首先用了最笨的办法，一个个单元格值计算，后来试了一下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