T-SQL 之 UNPIVOT、PIVOT【财年报表】

于 2022-01-21 08:17:16 发布
阅读量313 收藏
点赞数
分类专栏： T-SQL JavaScript 文章标签： sql 数据库 t-sql语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38567182/article/details/122606340
版权

###公司财年的数据报表

  • 需求是做公司财年的数据报表：
    【4月，5月，6月，7月，8月，9月，10月，11月，12月，(来年)01月，(来年)02月，(来年)03月】为一财年
    如：2020财年是
    【2019.4，2019.5，2019.6，2019.7，2019.8，2019.9，2019.10，2019.11，2019.12，2020.01，2020.02，2020.03】
    部分需求图
    ######公司高级开发师的数据库设计，我本人感觉设计不合理（实际开发中走过不该走的坑），职位太小只能硬着头皮配合
    数据表结构设计
    ######在接到需求后，我的第一想法，是想办法数据以这样的方式展示出来，在前端，稍微给点样式，后台基本不用管，只需要将sql丢进去执行就行了。
    这样才是横向，动态；且为一列
    ######从上面的需求和表结构的设计就能看出大概我的思路了；
  1. 选择财年后可以选择月份，此时月份是灵活变动的，【4月，5月，6月，(来年)01月，(来年)02月】所以不能固定写死（我的思路是拼SQL）
  2. 成个表结构是垂直行向设计，所以数据查询出来后必须做UNPIVOT转纵（横）处理；
  3. 在第二步中UNPIVOT转纵（横）处理其实并不是我想展示数据结构；所以在这里我需要PIVOT纵转多行（多行转多列）
    ######下面看我实现的实际步骤
  4. 首先我查询所有符合的数据，然后存入临时表（方面后面调用避免使用子查询）
    查询所有符合的数据，然后存入临时表
  5. 整个表结构是垂直行向设计，所以数据查询出来后必须做UNPIVOT转纵（横）处理；后续需要调用所以存入了临时表
    UNPIVOT转纵（横）处理
  6. 此时月份是灵活变动的，【4月，5月，6月，(来年)01月，(来年)02月】所以不能固定写死（我的思路是拼SQL）
    拼SQL
  7. @valuekey（用于在后面PIVOT纵转多行（多行转多列）时的【字段】）拼出的数据，这些字段是拼出的活的使用STUFF FOR XML PATH 拼SQL

SUM(ISNULL([2019-02Rate], 0)) AS [2019-02Rate],SUM(ISNULL([2019-02Resales_Money], 0)) AS [2019-02Resales_Money],SUM(ISNULL([2019-02Resales_Money_Target], 0)) AS [2019-02Resales_Money_Target],SUM(ISNULL([2019-04Rate], 0)) AS [2019-04Rate],SUM(ISNULL([2019-04Resales_Money], 0)) AS [2019-04Resales_Money],SUM(ISNULL([2019-04Resales_Money_Target], 0)) AS [2019-04Resales_Money_Target],SUM(ISNULL([2019-06Rate], 0)) AS [2019-06Rate],SUM(ISNULL([2019-06Resales_Money], 0)) AS [2019-06Resales_Money],SUM(ISNULL([2019-06Resales_Money_Target], 0)) AS [2019-06Resales_Money_Target],SUM(ISNULL([2019-10Rate], 0)) AS [2019-10Rate],SUM(ISNULL([2019-10Resales_Money], 0)) AS [2019-10Resales_Money],SUM(ISNULL([2019-10Resales_Money_Target], 0)) AS [2019-10Resales_Money_Target],SUM(ISNULL([2019-11Rate], 0)) AS [2019-11Rate],SUM(ISNULL([2019-11Resales_Money], 0)) AS [2019-11Resales_Money],SUM(ISNULL([2019-11Resales_Money_Target], 0)) AS [2019-11Resales_Money_Target],SUM(ISNULL([2019-12Rate], 0)) AS [2019-12Rate],SUM(ISNULL([2019-12Resales_Money], 0)) AS [2019-12Resales_Money],SUM(ISNULL([2019-12Resales_Money_Target], 0)) AS [2019-12Resales_Money_Target],SUM(ISNULL([2020-01Rate], 0)) AS [2020-01Rate],SUM(ISNULL([2020-01Resales_Money], 0)) AS [2020-01Resales_Money],SUM(ISNULL([2020-01Resales_Money_Target], 0)) AS [2020-01Resales_Money_Target],SUM(ISNULL([2020-02Rate], 0)) AS [2020-02Rate],SUM(ISNULL([2020-02Resales_Money], 0)) AS [2020-02Resales_Money],SUM(ISNULL([2020-02Resales_Money_Target], 0)) AS [2020-02Resales_Money_Target],SUM(ISNULL([2020-04Rate], 0)) AS [2020-04Rate],SUM(ISNULL([2020-04Resales_Money], 0)) AS [2020-04Resales_Money],SUM(ISNULL([2020-04Resales_Money_Target], 0)) AS [2020-04Resales_Money_Target]

  1. @mtr(是后面PIVOT纵转多行的 PIVOT FOR （需要转多列的【值】）

[2019-02Rate],[2019-02Resales_Money],[2019-02Resales_Money_Target],[2019-04Rate],[2019-04Resales_Money],[2019-04Resales_Money_Target],[2019-06Rate],[2019-06Resales_Money],[2019-06Resales_Money_Target],[2019-10Rate],[2019-10Resales_Money],[2019-10Resales_Money_Target],[2019-11Rate],[2019-11Resales_Money],[2019-11Resales_Money_Target],[2019-12Rate],[2019-12Resales_Money],[2019-12Resales_Money_Target],[2020-01Rate],[2020-01Resales_Money],[2020-01Resales_Money_Target],[2020-02Rate],[2020-02Resales_Money],[2020-02Resales_Money_Target],[2020-04Rate],[2020-04Resales_Money],[2020-04Resales_Money_Target]

  1. 把第二步的临时表 #Temapet，进行PIVOT的纵转多行处理
--纵转多行
SET @query = N'
SELECT
District,
[District Desc] AS District_Desc,
Area,
[Area Description] AS Area_Description,
Small_Area,
Small_Area_Desc,
City,
[City Description] AS City_Description,
ISNULL(#TE.[Branch Code], BH.[Branch Code])AS Branch_Code,
[Branch Description] AS Branch_Description,
#TE.*,
Temp.SUM_Money,
Temp.SUM_Target,
Temp.SUM_Rate
FROM Branch AS BH
LEFT JOIN (
SELECT [Branch Code],
SUM(ISNULL(Resales_Money,0))AS SUM_Money,
SUM(ISNULL(Resales_Money_Target,0))AS SUM_Target,
--CAST(SUM(ISNULL(Rate,0))*100 as varchar)+''%'' AS SUM_Rate
SUM(ISNULL(Rate,0))AS SUM_Rate
FROM #TEMP 
GROUP BY [Branch Code]
)Temp ON Temp.[Branch Code] = BH.[Branch Code]
INNER JOIN(
SELECT pvt.[Branch Code],'+@valuekey+'
FROM #Temapet
    PIVOT (MAX(Valuest)
          FOR Valuekey IN ('+@mtr+')) pvt
GROUP BY pvt.[Branch Code]
)AS #TE ON #TE.[Branch Code] = BH.[Branch Code] ;'
PRINT @query
EXECUTE(@query);
  1. 最后执行的结果展示
    最终展示结构
    实现效果
  2. 最后把整体SQL发出来，仅供参考
SELECT
  BH.District AS District,
  BH.Area,
  BH.Small_Area,
  BH.City,
  ISNULL(RRM.Branch_Code, RRMT.Branch_Code) [Branch Code],
      CONVERT(VARCHAR(7), ISNULL(RRM.Month_Date, RRMT.Month_Date), 120) AS Month_Date,
      ISNULL(RRM.Resales_Money, 0) Resales_Money,
      ISNULL(RRMT.Resales_Money_Target, 0) Resales_Money_Target,
      CASE WHEN ISNULL(RRMT.Resales_Money_Target, 0) = 0 THEN 0
            ELSE ISNULL(RRM.Resales_Money, 0) / RRMT.Resales_Money_Target END AS Rate
INTO  #TEMP
FROM  dbo.Branch AS BH 
      LEFT JOIN tbCRM_Report_Resales_Monthly AS RRM
  ON RRM.Branch_Code=BH.[Branch Code]
      FULL OUTER JOIN tbCRM_Report_Resales_Monthly_Target AS RRMT
      ON RRMT.Branch_Code = RRM.Branch_Code
      AND RRM.Month_Date = RRMT.Month_Date
WHERE BH.District=209 AND (RRM.Branch_Code IS NOT NULL OR RRMT.Branch_Code IS NOT NULL) AND BH.[Branch Code] IN (SELECT [Branch Code] FROM Branch WHERE [District] IN (Select [District] From [Branch POS] Where [Branch Code] = 3158))
--行转纵
SELECT TEMP.[Branch Code],TEMP.Month_Date,TEMP.Month_Date+TEMP.Valuekey Valuekey,TEMP.Valuest
INTO #Temapet
FROM #TEMP
UNPIVOT
(
[Valuest]
FOR [Valuekey] IN([Resales_Money], [Resales_Money_Target], [Rate])
) AS TEMP
--拼sql
DECLARE @valuekey AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @mtr AS NVARCHAR(MAX)
SELECT @valuekey = STUFF(
(
SELECT N',SUM(ISNULL(['+[Valuekey]+'], 0)) AS '+ QUOTENAME([Valuekey])
FROM #Temapet
GROUP BY[Valuekey]
ORDER BY[Valuekey]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,''),
@mtr=STUFF(
(
SELECT ',' + QUOTENAME([Valuekey])FROM #Temapet
GROUP BY[Valuekey]
ORDER BY[Valuekey]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
PRINT @valuekey
PRINT @mtr
--纵转多行
SET @query = N'
SELECT
District,
[District Desc] AS District_Desc,
Area,
[Area Description] AS Area_Description,
Small_Area,
Small_Area_Desc,
City,
[City Description] AS City_Description,
ISNULL(#TE.[Branch Code], BH.[Branch Code])AS Branch_Code,
[Branch Description] AS Branch_Description,
#TE.*,
Temp.SUM_Money,
Temp.SUM_Target,
Temp.SUM_Rate
FROM Branch AS BH
LEFT JOIN (
SELECT [Branch Code],
SUM(ISNULL(Resales_Money,0))AS SUM_Money,
SUM(ISNULL(Resales_Money_Target,0))AS SUM_Target,
--CAST(SUM(ISNULL(Rate,0))*100 as varchar)+''%'' AS SUM_Rate
SUM(ISNULL(Rate,0))AS SUM_Rate
FROM #TEMP 
GROUP BY [Branch Code]
)Temp ON Temp.[Branch Code] = BH.[Branch Code]
INNER JOIN(
SELECT pvt.[Branch Code],'+@valuekey+'
FROM #Temapet
    PIVOT (MAX(Valuest)
          FOR Valuekey IN ('+@mtr+')) pvt
GROUP BY pvt.[Branch Code]
)AS #TE ON #TE.[Branch Code] = BH.[Branch Code] ;'
PRINT @query
EXECUTE(@query);

回思：
数据表设计不合理，应该开始就是纵（横）向设计；导致中间多余的UNPIVOT转纵（横）处理，欢迎大家评论。

YANG-Live
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
查询sql,适用于报表
08-21
一段非常好的sql,结合了各种函数写出来的报表,学习程度非常高
SQL Server 行列相互转换命令：PIVOT和UNPIVOT使用详解
a572893208的博客
03-04 1071
一、使用PIVOT和UNPIVOT命令的SQL Server版本要求 1.数据库的最低版本要求为SQL Server 2005 或更高。 2.必须将数据库的兼容级别设置为90 或更高。 3.查看我的数据库版本及兼容级别。 如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入：print @@version，运行...
T-Sql中的pivot和unpivot
weixin_33994429的博客
12-27 67
写在前面 今天休息，抽空了解下pivot和unpivot，记得老师讲行转列的时候，貌似提到过，不过他说的最多的就是“这个你们私下可以自己学，很简单的...”，简单你咋不讲呢，不吐槽他了，还是好好整理下上午自学的内容吧。 pivot运算符实现透视转换 透视转换是指：将一种数据从行转为列的状态的处理手段。主要经历三个逻辑处理阶段(分组，扩展，聚合)。 sql2005引入...
SQL Server中的报表–结合T-SQL和DAX查询以生成有效的报表
culuo4781的博客
07-19 986
介绍 (Introduction) With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends. 在当今充满挑战的经济时代，管理和纠正不断变化的销售模式和趋势变得越来越...
T-SQL PIVOT 行列转换
weixin_33836874的博客
03-05 133
前面几个例子： http://www.cnblogs.com/insus/articles/1969896.html  http://www.cnblogs.com/insus/articles/1970577.html http://www.cnblogs.com/insus/articles/1970707.html 全是SQL Server行列转换或是列行转换的相关例子。不过在SQL...
SQL行列转换 Pivot UnPivot
05-27
SQL行列转换 Pivot UnPivot
microsoft sql server 2008 T-sql fundamentals
06-14
chapter1:background to t-sql query chapter2:single-table queries chapter3:joins chapter4:subqueries chapter5:table expressions chapter6:set operations chapter7:pivot ,unpivot,and groupint sets chapter...
SQL知识点之列转行Unpivot函数
09-09
主要给大家介绍了关于SQL知识点之列转行Unpivot函数的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用SQL具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
深入SQL中PIVOT 行列转换详解
09-10
T-SQL语句中，Pivot运算符用于在列和行之间对数据进行旋转或透视转换，PIVOT命令可以实现数据表的列转行，同时执行聚合运算，UNPIVOT则与其相反，实现数据的行转列。
pivot与unpivot函数
08-28
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
财务数据库设计+sql
05-23
财务数据库设计文档+思路，包含数据库设计原理及具体实现sql
财务报表连打
07-03
财务报表的连续打印，不再浪费纸资源。财务报表必备技能
SQL Server Report Server 报表用户权限T-SQL查询
weixin_30950887的博客
04-03 267
/************************************************************************** 　　　　　　查询用户在报表上的权限 **************************************************************************/ use ReportServer go ...
T-SQL之数据表操作
Hugu
04-05 476
1、数据表的创建(CREATE TABLE语句)创建数据库的语法：CREATE TABLE 表名称( <列名称1> <数据类型> [列级完整性约束定义] [,<列名称2> <数据类型> [列级完整性约束定义]] [,<列名称3> <数据类型> [列级完整性约束定义]...] [,表级完整性约束定义])数据类型约束约束的分类：(...
月考勤报表的TSQL查询
weixin_33859231的博客
09-09 481
为什么80%的码农都做不了架构师？>>> ...
t-sql中pivot用法(行列转换)
天外有天，人外有人。 专注品质 不断创新 让精益求精成为习惯
09-11 4716
从另一张表找的3974行是张三,8319行是李四,3051行是王五; 从stu_score查id,stu_id两列,聚合求出 得出张三,李四,王五在stu_score中的记录条数  select [3974] as 张三, [8319] as 李四, [3051] as 王五 from  (select id,stu_id from stu_score) as s pivot
[T-SQL] PIVOT用法
一日一学！
11-14 293
-- 会员表 CREATE TABLE T_MEMBER ( MEMBER_ID CHAR(02) PRIMARY KEY, MEMBER_NAME VARCHAR(20) ) -- 商品表 CREATE TABLE T_PRODUCT ( PRODUCT_ID CHAR(02) PRIMARY KEY, PRODUCT_NAME VARCHAR(20) ) -- ...
SQL puzzles and answers读书笔记——财年表
weixin_30586257的博客
08-16 98
最近开始阅读数据库大师Joe Celko的《SQL PUZZLES AND ANSWERS》一书。这本书每篇都是一个SQL谜题，涉及数据库设计、开发。我看的是英文第二版，在这里做个读书笔记，顺便用T-SQL和PL/SQL来实现其中的代码。 谜题1——财年表 我们首先来创建一张表（以下代码在SQL Server 2008和Oracle上都能运行通过）： create table FiscalY...
sql unpivot
最新发布
09-06
SQL的unpivot函数用于将列转换为行。通过使用unpivot函数，我们可以将原始查询结果中的列名作为新的一列，并将对应的数据列作为另一列。unpivot函数的语法如下： unpivot ( 新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名) ) 例如，假设我们有一个名为T_PIVOT_TEST_1的表，其中包含三列：COL_1，COL_2和COL_3，我们可以使用unpivot函数将这三列转换为行。以下是示例查询的语法： SELECT * FROM T_PIVOT_TEST_1 UNPIVOT ( aa FOR bb IN (COL_1, COL_2, COL_3) ); 在执行此查询后，原始表中的每一行将被转换为三行，其中新增值所在列的列名为aa，新增列转为行后所在列的列名为bb。这样，我们就可以将原本的列转换为行并进行进一步的分析或处理。<span class="em">1</span><span class="em">2</span><span class="em">3</span><span class="em">4</span>

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值