前言

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

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

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

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

資料來源

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

Table: T_CompanyInfo

Table: T_PayInfo

Table: T_RevInfo

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

使用 PIVOT 轉置表格

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


SELECT PayDate, [10001] AS 'A Company', [10002] AS 'B Company'
FROM
(
SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
) AS STable
PIVOT
(
SUM(PayMoney)
FOR
CompanyID IN ([10001],[10002])
) AS PTable

　　查詢結果如下

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


SELECT RevDate, [10001] AS 'A Company', [10002] AS 'B Company'
FROM
(
SELECT CompanyID, RevDate, RevMoney FROM T_RevInfo
) AS STable
PIVOT
(
SUM(RevMoney)
FOR
CompanyID IN ([10001],[10002])
) AS PTable

使用 CTE 一般資料表運算式

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


WITH TA AS
(
SELECT PayDate, [10001] AS 'A Company', [10002] AS 'B Company'
FROM
(
SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
) AS STable
PIVOT
(
SUM(PayMoney)
FOR
CompanyID IN ([10001],[10002])
) AS PTable
),
TB AS
(
SELECT RevDate, [10001] AS 'A Company', [10002] AS 'B Company'
FROM
(
SELECT CompanyID, RevDate, RevMoney FROM T_RevInfo
) AS STable
PIVOT
(
SUM(RevMoney)
FOR
CompanyID IN ([10001],[10002])
) AS PTable
)
SELECT * FROM TA FULL OUTER JOIN TB ON TA.PayDate = TB.RevDate

　　而執行出來的結果如下

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

動態串接語法

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


SELECT PayDate, 
   [10001] AS 'A Company', [10002] AS 'B Company', [10003] AS 'C Company' ..... -- 需要動態增長
FROM
(
SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
) AS STable
PIVOT
(
SUM(PayMoney)
FOR
CompanyID IN ([10001],[10002],[10003],.....) -- 需要動態增長
) AS PTable

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

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


DECLARE @PivotColumns VARCHAR(MAX)
SET @PivotColumns = (
SELECT STUFF(
    (SELECT ',[' + CAST(ID AS VARCHAR) + '] AS ''' + CompanyName + ''''
       FROM T_CompanyInfo
        FOR XML PATH(''),type).value('.','NVARCHAR(max)'),1,1,'') AS [COMPANY] )
SELECT @PivotColumns

　　輸出結果如下

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


DECLARE @ForPivotColumns VARCHAR(MAX)
SET @ForPivotColumns = (
SELECT STUFF(
    (SELECT ',[' + CAST(ID AS VARCHAR) + ']'
       FROM T_CompanyInfo
        FOR XML PATH(''),type).value('.','NVARCHAR(max)'),1,1,'') AS [COMPANY] )
SELECT @ForPivotColumns

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


DECLARE @PivotColumns VARCHAR(MAX)
SET @PivotColumns = (
SELECT STUFF(
    (SELECT ',[' + CAST(ID AS VARCHAR) + '] AS ''' + CompanyName + ''''
       FROM T_CompanyInfo
        FOR XML PATH(''),type).value('.','NVARCHAR(max)'),1,1,'') AS [COMPANY] )
--SELECT @PivotColumns 

DECLARE @ForPivotColumns VARCHAR(MAX)
SET @ForPivotColumns = (
SELECT STUFF(
    (SELECT ',[' + CAST(ID AS VARCHAR) + ']'
       FROM T_CompanyInfo
        FOR XML PATH(''),type).value('.','NVARCHAR(max)'),1,1,'') AS [COMPANY] )
--SELECT @ForPivotColumns 

DECLARE @FinalColumns VARCHAR(MAX)
SET @FinalColumns = 'ISNULL(TA.PayDate, TB.RevDate) AS ''Date'', ' + 
(SELECT STUFF(
    (SELECT ',TA.[' + CompanyName + '] AS ''' + CompanyName + 'Pay'''
       FROM T_CompanyInfo
        FOR XML PATH(''),type).value('.','NVARCHAR(max)'),1,1,'') AS [COMPANY] )
    + ',' + 
(SELECT STUFF(
    (SELECT ',TB.[' + CompanyName + '] AS ''' + CompanyName + 'Rev'''
       FROM T_CompanyInfo
        FOR XML PATH(''),type).value('.','NVARCHAR(max)'),1,1,'') AS [COMPANY] )
SELECT @FinalColumns 

DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
WITH TA AS
(
SELECT PayDate, ' + @PivotColumns + '
FROM
(
SELECT CompanyID, PayDate, PayMoney FROM T_PayInfo
) AS STable
PIVOT
(
SUM(PayMoney)
FOR
CompanyID IN (' + @ForPivotColumns + ')
) AS PTable
),
TB AS
(
SELECT RevDate, ' + @PivotColumns + '
FROM
(
SELECT CompanyID, RevDate, RevMoney FROM T_RevInfo
) AS STable
PIVOT
(
SUM(RevMoney)
FOR
CompanyID IN (' + @ForPivotColumns + ')
) AS PTable
)
SELECT ' + @FinalColumns + ' FROM TA FULL OUTER JOIN TB ON TA.PayDate = TB.RevDate
'
EXEC(@SQL)

　　查詢結果如下

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

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


DECLARE @CompanyCASE1 VARCHAR(8000);
SET @CompanyCASE1 = '';
SELECT @CompanyCASE1 = @CompanyCASE1 + ',CASE WHEN CompanyID = ''' + CAST(ID AS VARCHAR) + ''' THEN SUM(PayMoney) ELSE 0 END AS ''' + CAST(ID AS VARCHAR) + ''''
FROM T_CompanyInfo 
SET @CompanyCASE1 = (SELECT SUBSTRING(@CompanyCASE1, 2, len(@CompanyCASE1)))
--SELECT @CompanyCASE1

DECLARE @CompanyCASE2 VARCHAR(8000);
SET @CompanyCASE2 = '';
SELECT @CompanyCASE2 = @CompanyCASE2 + ',CASE WHEN CompanyID = ''' + CAST(ID AS VARCHAR) + ''' THEN SUM(RevMoney) ELSE 0 END AS ''' + CAST(ID AS VARCHAR) + ''''
FROM T_CompanyInfo 
SET @CompanyCASE2 = (SELECT SUBSTRING(@CompanyCASE2, 2, len(@CompanyCASE2)))
--SELECT @CompanyCASE2

-- Gen SUM columns
DECLARE @CompanySUM VARCHAR(8000);
SET @CompanySUM = '';
SELECT @CompanySUM = @CompanySUM + ', SUM([' + CAST(ID AS VARCHAR) + ']) AS ''' + CompanyName + ''''
FROM T_CompanyInfo
SET @CompanySUM = 'Date,' + (SELECT SUBSTRING(@CompanySUM, 2, len(@CompanySUM)))
--SELECT @CompanySUM

DECLARE @FinalColumns VARCHAR(8000)
DECLARE @TEMP1 VARCHAR(8000)
DECLARE @TEMP2 VARCHAR(8000)
SET @TEMP1 = '';
SELECT @TEMP1 = @TEMP1 + ',TA.[' + CompanyName + '] AS ''' + CompanyName + 'Pay'''
FROM T_CompanyInfo
SET @TEMP1 = (SELECT SUBSTRING(@TEMP1, 2, len(@TEMP1)))
SET @TEMP2 = '';
SELECT @TEMP2 = @TEMP2 + ',TB.[' + CompanyName + '] AS ''' + CompanyName + 'Rev'''
FROM T_CompanyInfo
SET @TEMP2 = (SELECT SUBSTRING(@TEMP2, 2, len(@TEMP2)))
SET @FinalColumns = 'ISNULL(TA.Date, TB.Date) AS Date,' + @TEMP1 + ',' + @TEMP2
--SELECT @FinalColumns

DECLARE @SQL VARCHAR(8000) 
SET @SQL = ' 
            SELECT ' + @FinalColumns + '
            FROM 
            ( 
            SELECT ' + @CompanySUM + ' 
            FROM   
            ( 
            SELECT PayDate AS ''Date'',' + @CompanyCASE1 + ' 
            FROM T_PayInfo
            GROUP BY PayDate, CompanyID  
            ) A 
            GROUP BY Date 
            )  
            TA FULL OUTER JOIN  
            ( 
            SELECT ' + @CompanySUM + ' 
            FROM  
            ( 
            SELECT RevDate AS ''Date'',' + @CompanyCASE2 + ' 
FROM T_RevInfo
            GROUP BY RevDate, CompanyID 
            ) B 
            GROUP BY Date 
            )  
            TB ON TA.Date = TB.Date 
            ' 
EXEC(@SQL)

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