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

2020-06-14 本文已影响0人 YANG_LIVE

公司财年的数据报表

需求是做公司财年的数据报表：
【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丢进去执行就行了。

这样才是横向，动态；且为一列

从上面的需求和表结构的设计就能看出大概我的思路了；

选择财年后可以选择月份，此时月份是灵活变动的，【4月，5月，6月，(来年)01月，(来年)02月】所以不能固定写死（我的思路是拼SQL）
成个表结构是垂直行向设计，所以数据查询出来后必须做UNPIVOT转纵（横）处理；
在第二步中UNPIVOT转纵（横）处理其实并不是我想展示数据结构；所以在这里我需要PIVOT纵转多行（多行转多列）

下面看我实现的实际步骤

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

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

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

拼SQL
@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]

@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]

把第二步的临时表 #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);

最后执行的结果展示

最终展示结构
实现效果
最后把整体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【财年报表】

公司财年的数据报表

公司高级开发师的数据库设计，我本人感觉设计不合理（实际开发中走过不该走的坑），职位太小只能硬着头皮配合

在接到需求后，我的第一想法，是想办法数据以这样的方式展示出来，在前端，稍微给点样式，后台基本不用管，只需要将sql丢进去执行就行了。

从上面的需求和表结构的设计就能看出大概我的思路了；

下面看我实现的实际步骤

猜你喜欢

热点阅读