使用PIVOT目前只Support一個彙總資料行，那如何有多個彙總資料行要如何處理呢?
有朋友問關於「SQL分组求和」的問題如下，
資料如下，
日期（PRT_Date） 姓名(PRT_EmpName) 部门(PRT_Deprt) 金额(PRT_Amt) 数量(PRT_Qty)
2008-1-12 刘 部门1 100 10
2008-1-12 张 部门2 100 10
2008-1-13 刘 部门1 100 10
2008-1-19 张 部门2 300 30
2008-2-12 刘 部门1 120 12
2008-2-19 张 部门2 300 30
2008-3-12 刘 部门1 130 13
查詢得到以下的結果，
姓名 部门 2008-01 数量 2008-02 数量 2008-03 数量
刘 部门1 200 20 120 12 130 13
张 部门2 400 40 300 30 null null
以下先建立測試資料，
CREATE TABLE #t1
(
prt_date DATETIME
, prt_empname NVARCHAR(30)
, prt_deprt NVARCHAR(30)
, prt_amt DECIMAL
, prt_qty int
);
INSERT INTO #t1 VALUES('2008-1-12', N'刘', N'部门1', 100, 10);
INSERT INTO #t1 VALUES('2008-1-12', N'张', N'部门2', 100, 10);
INSERT INTO #t1 VALUES('2008-1-13', N'刘', N'部门1', 100, 10);
INSERT INTO #t1 VALUES('2008-1-19', N'张', N'部门2', 300, 30);
INSERT INTO #t1 VALUES('2008-2-12', N'刘', N'部门1', 120, 12);
INSERT INTO #t1 VALUES('2008-2-19', N'张', N'部门2', 300, 30);
INSERT INTO #t1 VALUES('2008-3-12', N'刘', N'部门1', 130, 13);
SELECT * FROM #t1;
很直覺可以使用PIVOT來處理，所以可以找出各年月、部門、姓名的金額及數量彙總資料，如下，
--PIVOT amt
SELECT *
FROM ( SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym ,
prt_deprt ,
prt_empname ,
prt_amt
FROM #t1
) AS SourceTable PIVOT
( SUM(prt_amt) FOR prt_ym IN ( [2008-01], [2008-02], [2008-03] ) ) AS pivotAMT;
--PIVOT qty
SELECT *
FROM ( SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym ,
prt_deprt ,
prt_empname ,
prt_qty
FROM #t1
) AS SourceTable PIVOT
( SUM(prt_qty) FOR prt_ym IN ( [2008-01], [2008-02], [2008-03] ) ) AS pivotQTY;
那要將金額及數量彙總資料合併起來的話，就可以透過CROSS APPLY來合併，如下，
SELECT t2.* ,
pivotQTY.[2008-01] AS [2008-01QTY] ,
pivotQTY.[2008-02] AS [2008-02QTY] ,
pivotQTY.[2008-03] AS [2008-03QTY]
FROM ( SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym ,
prt_deprt ,
prt_empname ,
prt_qty
FROM #t1
) AS SourceTable PIVOT ( SUM(prt_qty) FOR prt_ym IN ( [2008-01],
[2008-02],
[2008-03] ) ) AS pivotQTY
CROSS APPLY ( SELECT *
FROM ( SELECT LEFT(CONVERT(VARCHAR, prt_date, 20),
7) AS prt_ym ,
prt_deprt ,
prt_empname ,
prt_amt
FROM #t1
) AS SourceTable PIVOT
( SUM(prt_amt) FOR prt_ym IN ( [2008-01], [2008-02], [2008-03] ) ) AS pivotAMT
WHERE pivotQTY.prt_deprt = pivotAMT.prt_deprt
AND pivotQTY.prt_empname = pivotAMT.prt_empname
) t2
以上是寫死欄位名稱([2008-01], [2008-02], [2008-03])，如果需要動態的欄位名稱，可以參考「使用PIVOT」，先取出欄位名稱，再使用EXECUTE來執行，如下，
--use dynamic
--先取出欄位名稱
DECLARE @qty_columns NVARCHAR(MAX) ,
@amt_columns NVARCHAR(MAX)
SELECT @qty_columns = COALESCE(@qty_columns + ',[' + N'数量-' + RTRIM(T.prt_ym)
+ ']', '[' + N'数量-' + RTRIM(T.prt_ym) + ']') ,
@amt_columns = COALESCE(@amt_columns + ',[' + N'金额-' + RTRIM(T.prt_ym)
+ ']', '[' + N'金额-' + RTRIM(T.prt_ym) + ']')
FROM ( SELECT DISTINCT
LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym
FROM #t1
) T
ORDER BY T.prt_ym
PRINT @qty_columns
PRINT @amt_columns
--將欄位名稱串接進去
EXECUTE('SELECT T2.*, ' + @qty_columns + '
FROM
(SELECT N''数量-'' + LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname, prt_qty
FROM #t1
) AS X
PIVOT
(
SUM(prt_qty)
FOR prt_ym IN
(' + @qty_columns + ')
) AS PVT
CROSS APPLY (
SELECT *
FROM
(SELECT N''金额-'' + LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname, prt_amt
FROM #t1
) AS X
PIVOT
(
SUM(prt_amt)
FOR prt_ym IN
(' + @amt_columns + ')
) AS AMT_PVT
WHERE PVT.prt_deprt = AMT_PVT.prt_deprt AND PVT.prt_empname = AMT_PVT.prt_empname ) T2');
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
