1. 首页
  2. IT资讯

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

前言


  最近碰到一個想要將兩個不同表的統計金額合併,並且要以日期為統計區段列,而欄位是會動態成長的一個需求,產出結果如下圖。

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

  碰到這個問題時,第一時間想到就是用 PIVOT 來處理,但是這個情況有附加兩個條件如下

  1. 公司欄位會動態成長
  2. 支出與收入來自兩個不同資料表

 

  所以針對這兩個條件還需要個別做處理,第一個動態成長的條件可以使用串接字串的方式來處理,第二個合併兩個不同資料表的問題可以使用 CTE 搭配 JOIN 來處理。

 

資料來源


  首先先來看一下資料來源的資料表,如下

Table: T_CompanyInfo

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

Table: T_PayInfo

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

Table: T_RevInfo

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

 

  在 T_CompanyInfo 的公司清單是有會繼續成長的可能,而 T_PayInfo 與 T_RevInfo 分別是支出與收入的紀錄。

 

使用 PIVOT 轉置表格


  瞭解來源資料的內容後,以我的習慣我會先將完整的 T-SQL 語法撰寫出來在考慮動態的部分,所以第一步先撰寫靜態的 T-SQL 語法,先針對 T_PayInfo 使用 PIVOT 將資料依照日期轉至表格,語法如下 (關於 PIVOT 的使用可以參考上一篇文章)。

view source

print?

01 SELECT PayDate, [10001] AS ‘A Company’, [10002] AS ‘B Company’
02 FROM
03 (
04 SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
05 ) AS STable
06 PIVOT
07 (
08 SUM(PayMoney)
09 FOR
10 CompanyID IN ([10001],[10002])
11 ) AS PTable

 

  查詢結果如下

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

 

  由上圖可以看到 T_PayInfo 表格已經轉置成由日期為主的塞選,而 T_RevInfo 則也一樣畫葫蘆就能夠產生出一樣的結果,語法如下。

view source

print?

01 SELECT RevDate, [10001] AS ‘A Company’, [10002] AS ‘B Company’
02 FROM
03 (
04 SELECT CompanyID, RevDate, RevMoney FROM T_RevInfo
05 ) AS STable
06 PIVOT
07 (
08 SUM(RevMoney)
09 FOR
10 CompanyID IN ([10001],[10002])
11 ) AS PTable

 

使用 CTE 一般資料表運算式


  透過使用 CTE 的方式能夠建立暫存結果集,並將此暫存結果集進行 FULL OUTER JOIN 來合併兩個表格,語法如下

view source

print?

01 WITH TA AS
02 (
03 SELECT PayDate, [10001] AS ‘A Company’, [10002] AS ‘B Company’
04 FROM
05 (
06 SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
07 ) AS STable
08 PIVOT
09 (
10 SUM(PayMoney)
11 FOR
12 CompanyID IN ([10001],[10002])
13 ) AS PTable
14 ),
15 TB AS
16 (
17 SELECT RevDate, [10001] AS ‘A Company’, [10002] AS ‘B Company’
18 FROM
19 (
20 SELECT CompanyID, RevDate, RevMoney FROM T_RevInfo
21 ) AS STable
22 PIVOT
23 (
24 SUM(RevMoney)
25 FOR
26 CompanyID IN ([10001],[10002])
27 ) AS PTable
28 )
29 SELECT * FROM TA FULL OUTER JOIN TB ON TA.PayDate = TB.RevDate

 

  而執行出來的結果如下

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

 

  進行到此,已經可以看出與最後需要的結果差不多了,將下來就是要考慮公司是會動態增長的,所以必須要使用動態串接的方式來帶入公司欄位名稱。

 

動態串接語法


  在動態串接的部分,首先我們知道公司名稱是來自 T_CompanyInfo 資料表並且會繼續成長,回看到使用 PIVOT 的時候,會發現樞紐資料行必須要隨著 T_PayInfo 資料表中 CompanyID 欄位內的公司編號增長,如下

view source

print?

01 SELECT PayDate,
02    [10001] AS ‘A Company’, [10002] AS ‘B Company’, [10003] AS ‘C Company’ ….. — 需要動態增長
03 FROM
04 (
05 SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
06 ) AS STable
07 PIVOT
08 (
09 SUM(PayMoney)
10 FOR
11 CompanyID IN ([10001],[10002],[10003],…..) — 需要動態增長
12 ) AS PTable

 

  所以在此必須將這兩個位置的內容使用動態產生的字串帶入處理,再回到 T_CompanyInfo 資料表中撈出來的 ID 就是目前需要塞選的公司編號,但是使用 SELECT 撈出來的是列表狀的,那要怎麼把一列列的公司編號串成只有一列的字串呢?

 

  要將一列列的資料合併成一個欄位的字串可以使用 FOR XML PATH 方法,語法如下

view source

print?

1 DECLARE @PivotColumns VARCHAR(MAX)
2 SET @PivotColumns = (
3 SELECT STUFF(
4     (SELECT ‘,[‘ + CAST(ID AS VARCHAR) + ‘] AS ”’ + CompanyName + ””
5        FROM T_CompanyInfo
6         FOR XML PATH(”),type).value(‘.’,’NVARCHAR(max)’),1,1,”) AS [COMPANY] )
7 SELECT @PivotColumns

 

  輸出結果如下

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

 

  由上圖可以發現已經將一列列的公司編號串成一列字串,而在 FOR CompanyID IN (……..) 此位置的字串一樣使用此方法即可串出動態字串,語法如下

view source

print?

1 DECLARE @ForPivotColumns VARCHAR(MAX)
2 SET @ForPivotColumns = (
3 SELECT STUFF(
4     (SELECT ‘,[‘ + CAST(ID AS VARCHAR) + ‘]’
5        FROM T_CompanyInfo
6         FOR XML PATH(”),type).value(‘.’,’NVARCHAR(max)’),1,1,”) AS [COMPANY] )
7 SELECT @ForPivotColumns

 

  最後只需要將原本的 T-SQL 語法修改成字串的格式,在 PIVOT 欄位的部分替換成動態產生的字串即可,語法如下

view source

print?

01 DECLARE @PivotColumns VARCHAR(MAX)
02 SET @PivotColumns = (
03 SELECT STUFF(
04     (SELECT ‘,[‘ + CAST(ID AS VARCHAR) + ‘] AS ”’ + CompanyName + ””
05        FROM T_CompanyInfo
06         FOR XML PATH(”),type).value(‘.’,’NVARCHAR(max)’),1,1,”) AS [COMPANY] )
07 –SELECT @PivotColumns
08  
09 DECLARE @ForPivotColumns VARCHAR(MAX)
10 SET @ForPivotColumns = (
11 SELECT STUFF(
12     (SELECT ‘,[‘ + CAST(ID AS VARCHAR) + ‘]’
13        FROM T_CompanyInfo
14         FOR XML PATH(”),type).value(‘.’,’NVARCHAR(max)’),1,1,”) AS [COMPANY] )
15 –SELECT @ForPivotColumns
16  
17 DECLARE @FinalColumns VARCHAR(MAX)
18 SET @FinalColumns = ‘ISNULL(TA.PayDate, TB.RevDate) AS ”Date”, ‘ +
19 (SELECT STUFF(
20     (SELECT ‘,TA.[‘ + CompanyName + ‘] AS ”’ + CompanyName + ‘Pay”’
21        FROM T_CompanyInfo
22         FOR XML PATH(”),type).value(‘.’,’NVARCHAR(max)’),1,1,”) AS [COMPANY] )
23     + ‘,’ +
24 (SELECT STUFF(
25     (SELECT ‘,TB.[‘ + CompanyName + ‘] AS ”’ + CompanyName + ‘Rev”’
26        FROM T_CompanyInfo
27         FOR XML PATH(”),type).value(‘.’,’NVARCHAR(max)’),1,1,”) AS [COMPANY] )
28 SELECT @FinalColumns
29  
30 DECLARE @SQL VARCHAR(MAX)
31 SET @SQL = ‘
32 WITH TA AS
33 (
34 SELECT PayDate, ‘ + @PivotColumns + ‘
35 FROM
36 (
37 SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
38 ) AS STable
39 PIVOT
40 (
41 SUM(PayMoney)
42 FOR
43 CompanyID IN (‘ + @ForPivotColumns + ‘)
44 ) AS PTable
45 ),
46 TB AS
47 (
48 SELECT RevDate, ‘ + @PivotColumns + ‘
49 FROM
50 (
51 SELECT CompanyID, RevDate, RevMoney FROM T_RevInfo
52 ) AS STable
53 PIVOT
54 (
55 SUM(RevMoney)
56 FOR
57 CompanyID IN (‘ + @ForPivotColumns + ‘)
58 ) AS PTable
59 )
60 SELECT ‘ + @FinalColumns + ‘ FROM TA FULL OUTER JOIN TB ON TA.PayDate = TB.RevDate
61
62 EXEC(@SQL)

 

  查詢結果如下

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

 

  如果 T_CompanyInfo 多加了一筆公司資料的話,執行相同 T-SQL 語法,返回結果如下

使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表

 

  另外補充說明,如果要在 SQL SERVER 2000 上執行此語法是不支援的,必須改以用以下的方法來撰寫,如下

view source

print?

01 DECLARE @CompanyCASE1 VARCHAR(8000);
02 SET @CompanyCASE1 = ”;
03 SELECT @CompanyCASE1 = @CompanyCASE1 + ‘,CASE WHEN CompanyID = ”’ + CAST(ID AS VARCHAR) + ”’ THEN SUM(PayMoney) ELSE 0 END AS ”’ + CAST(ID AS VARCHAR) + ””
04 FROM T_CompanyInfo
05 SET @CompanyCASE1 = (SELECT SUBSTRING(@CompanyCASE1, 2, len(@CompanyCASE1)))
06 –SELECT @CompanyCASE1
07  
08 DECLARE @CompanyCASE2 VARCHAR(8000);
09 SET @CompanyCASE2 = ”;
10 SELECT @CompanyCASE2 = @CompanyCASE2 + ‘,CASE WHEN CompanyID = ”’ + CAST(ID AS VARCHAR) + ”’ THEN SUM(RevMoney) ELSE 0 END AS ”’ + CAST(ID AS VARCHAR) + ””
11 FROM T_CompanyInfo
12 SET @CompanyCASE2 = (SELECT SUBSTRING(@CompanyCASE2, 2, len(@CompanyCASE2)))
13 –SELECT @CompanyCASE2
14  
15 — Gen SUM columns
16 DECLARE @CompanySUM VARCHAR(8000);
17 SET @CompanySUM = ”;
18 SELECT @CompanySUM = @CompanySUM + ‘, SUM([‘ + CAST(ID AS VARCHAR) + ‘]) AS ”’ + CompanyName + ””
19 FROM T_CompanyInfo
20 SET @CompanySUM = ‘Date,’ + (SELECT SUBSTRING(@CompanySUM, 2, len(@CompanySUM)))
21 –SELECT @CompanySUM
22  
23 DECLARE @FinalColumns VARCHAR(8000)
24 DECLARE @TEMP1 VARCHAR(8000)
25 DECLARE @TEMP2 VARCHAR(8000)
26 SET @TEMP1 = ”;
27 SELECT @TEMP1 = @TEMP1 + ‘,TA.[‘ + CompanyName + ‘] AS ”’ + CompanyName + ‘Pay”’
28 FROM T_CompanyInfo
29 SET @TEMP1 = (SELECT SUBSTRING(@TEMP1, 2, len(@TEMP1)))
30 SET @TEMP2 = ”;
31 SELECT @TEMP2 = @TEMP2 + ‘,TB.[‘ + CompanyName + ‘] AS ”’ + CompanyName + ‘Rev”’
32 FROM T_CompanyInfo
33 SET @TEMP2 = (SELECT SUBSTRING(@TEMP2, 2, len(@TEMP2)))
34 SET @FinalColumns = ‘ISNULL(TA.Date, TB.Date) AS Date,’ + @TEMP1 + ‘,’ + @TEMP2
35 –SELECT @FinalColumns
36  
37 DECLARE @SQL VARCHAR(8000)
38 SET @SQL = ‘
39             SELECT ‘ + @FinalColumns + ‘
40             FROM
41             (
42             SELECT ‘ + @CompanySUM + ‘
43             FROM  
44             (
45             SELECT PayDate AS ”Date”,’ + @CompanyCASE1 + ‘
46             FROM T_PayInfo
47             GROUP BY PayDate, CompanyID 
48             ) A
49             GROUP BY Date
50             ) 
51             TA FULL OUTER JOIN 
52             (
53             SELECT ‘ + @CompanySUM + ‘
54             FROM 
55             (
56             SELECT RevDate AS ”Date”,’ + @CompanyCASE2 + ‘
57 FROM T_RevInfo
58             GROUP BY RevDate, CompanyID
59             ) B
60             GROUP BY Date
61             ) 
62             TB ON TA.Date = TB.Date
63             ‘
64 EXEC(@SQL)

 

  剩下的 NULL 值就交由各位自行處理了,以上就是透過使用 FOR XML PATH、CTE、PIVOT 產生動態欄位統計的方法紀錄,當然如果有更好的方法或有甚麼問題也可以跟我說一下喔。

 

參考資料


使用 PIVOT 和 UNPIVOT

FOR XML (SQL Server)

WITH common_table_expression (Transact-SQL)

converting rows to one column sql server

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20200170/viewspace-1704594/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:℅傍ㄖ免沦陷dε鬼,转转请注明出处:http://www.cxybcw.com/191699.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code