美文网首页
T-SQL 之 UNPIVOT、PIVOT【财年报表】

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

作者: YANG_LIVE | 来源:发表于2020-06-14 00:51 被阅读0次

公司财年的数据报表

  • 需求是做公司财年的数据报表：
    【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纵转多行（多行转多列）
下面看我实现的实际步骤

  1. 首先我查询所有符合的数据，然后存入临时表（方面后面调用避免使用子查询）


    查询所有符合的数据，然后存入临时表

  2. 整个表结构是垂直行向设计，所以数据查询出来后必须做UNPIVOT转纵（横）处理；后续需要调用所以存入了临时表


    UNPIVOT转纵（横）处理

  3. 此时月份是灵活变动的，【4月，5月，6月，(来年)01月，(来年)02月】所以不能固定写死（我的思路是拼SQL）


    拼SQL
  4. @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转纵（横）处理，欢迎大家评论。

相关文章

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

    公司财年的数据报表 需求是做公司财年的数据报表：【4月，5月，6月，7月，8月，9月，10月，11月，12月，(来...

  • SQL行列转换

    行转列1.1、case when1.2、内置函数pivot 列转行2.1、unpivot2.2、union实现

  • ORACLE 行列转函数

    Oracle11g提供了新的行列转换操作：PIVOT(行转列)和UNPIVOT列转行。老版本使用decode来进行...

  • PIVOT、UNPIVOT 转换行与列

    可以使用 PIVOT 和 UNPIVOT 运算符将表值表达式更改为另一个表。 PIVOT 是将表达式某一列中的唯一...

  • MySQL学习之行列转换案例

    前言 Oracle中的pivot/unpivot函数可以很方便的帮助我们实现行列转换，但是MySQL并不支持。可以...

  • SQL中PIVOT 行列转换

    本文导读：T-SQL语句中，Pivot运算符用于在列和行之间对数据进行旋转或透视转换，PIVOT命令可以实现数据表...

  • DLA SQL技巧：行、列转换和JSON数据列展开

    1. 简介 在数据库SQL处理中，常常有行转列（Pivot）和列转行（Unpivot）的数据处理需求。本文以示例说...

  • 2019-07-12 Fiscal Year Setting

    财年是用于计算企业和其他组织的年度财务报表时间，在salesforce之中，提供了两种方式去设置财年。路径：Set...

  • sql中列转行unpivot、行转列pivot详细讲解||Mys

    目录：一、sqlserver列转行、行转列详解二、Oracle列转行、行转列详解三、DB2列转行、行转列详解四、M...

  • 2020-05-27

    账户 职位 操作 人事档案、请假、异动记录 监管：反馈与建议，满意度调查 收支记录、财年报表

网友评论

      本文标题：T-SQL 之 UNPIVOT、PIVOT【财年报表】

      本文链接：https://www.haomeiwen.com/subject/edmtxktx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|T-SQL 之 UNPIVOT、PIVOT【财年报表】|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！