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

RunCodeAjax

于 2022-04-19 20:53:34 发布

阅读量818

点赞数

分类专栏：数据库文章标签： sqlserver

本文链接：https://blog.youkuaiyun.com/RunCodeAjax/article/details/124283104

版权

数据库专栏收录该内容

4 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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