SQL Server PIVOT自動化

做此之前，一定要先看過下面2篇
之前做過的樞紐
資料轉向成列表

現在我們要結合這2個，讓樞紐能更自動化
樞紐對人類而言，在資料清單上能一目了然目前的狀況
不過對電腦而言，資料是一筆筆羅列下來，所以在製作樞紐時
我們都必需給他欄位名及對應的資料：

看出端倪了嗎？2002~2004年是手打進去的
這是只有3年，如果要每個月呢？ 或是每天？
啊不就打得很痛苦？

所以我們就要結合一個功能叫使用者定義資料類型：

簡單的來講就是讓使用者可以自訂像是 INT NVARCHAR 的類型

流程大致如下：

1. 定義一個TABLE的類型，並宣告該類型的變數
2. 利用之前的方法把資料「橫」起來  → 資料轉向成列表
3. 再把所有PIVOT指令串成字串  →  樞紐
4. 利用EXECUTE可以執行「字串」的特性來執行 (或是包成PROCEDURE)

第一步：

--先宣告自訂「使用者定差資料表類型」
CREATE TYPE ColumnDataList AS TABLE
(
 data NVARCHAR(20)
)

--如此，就可以將變數宣告成我們自訂的ColumnDataList
DECLARE @TT ColumnDataList
INSERT INTO @TT VALUES('aa'),('bb'),('cc')
SELECT * FROM @TT

第二步：

--建立一個函數將資料橫起來，注意自訂類型一定要READONLY
CREATE FUNCTION 欄位列表(@Table ColumnDataList READONLY) RETURNS NVARCHAR(MAX)
AS 
 BEGIN
  DECLARE @ColumnName NVARCHAR(MAX)
  SET @ColumnName = ''
  SELECT @ColumnName = @ColumnName + '['+ data +'],'
  FROM @Table
  
  IF LEN(@ColumnName) >0
   SET @ColumnName = SUBSTRING(@ColumnName,1,LEN(@ColumnName)-1)
  RETURN @ColumnName
 END



--完成後試看看，看資料有沒有橫放入資料列中
DECLARE @Table ColumnDataList
INSERT INTO @Table SELECT DISTINCT 職稱 FROM 員工
SELECT dbo.欄位列表(@Table)

DECLARE @Table ColumnDataList
INSERT INTO @Table SELECT DISTINCT 職稱 FROM 員工
SELECT dbo.欄位列表(@Table)

第三、四步：

DECLARE @TT ColumnDataList --宣告@TT為自訂的變數
DECLARE @sql NVARCHAR(MAX) --宣告執行的字串
--將每一年獨立放入變數@TT
INSERT INTO @TT SELECT DISTINCT YEAR(訂單日期) AS 訂單年 FROM 訂貨主檔 ORDER BY YEAR(訂單日期)

SET @sql ='
WITH TEMP
AS
(
SELECT YEAR(A.訂單日期) AS 訂單年,D.類別名稱,SUM(B.數量) AS 銷售量
  FROM 訂貨主檔 AS A 
  JOIN 訂貨明細 AS B ON A.訂單號碼 = B.訂單號碼
  JOIN 產品資料 AS C ON B.產品編號 = C.產品編號
  JOIN 產品類別 AS D ON C.類別編號 = D.類別編號
GROUP BY YEAR(A.訂單日期),D.類別名稱
) 
SELECT 類別名稱,' + (SELECT dbo.欄位列表(@TT)) +
' FROM TEMP PIVOT( SUM(銷售量) FOR 訂單年 IN(' + (SELECT dbo.欄位列表(@TT)) +')) AS PP'

--將指令印出來，應該會跟樞紐的指令一模一樣
PRINT(@sql)

--如果一模一樣，就進第四步執行
EXECUTE(@sql)

如此就完成了！！



還有別的做法及引伸題，下面是不用字串，用暫存表

SELECT YEAR(A.訂單日期) AS 訂單年,D.類別名稱,SUM(B.數量) AS 銷售量
INTO #Temp
FROM 訂貨主檔 AS A JOIN 訂貨明細 AS B ON A.訂單號碼=B.訂單號碼
 JOIN 產品資料 AS C ON B.產品編號=C.產品編號
 JOIN 產品類別 AS D ON C.類別編號=D.類別編號
GROUP BY YEAR(A.訂單日期),D.類別名稱

DECLARE @sql NVARCHAR(MAX)
DECLARE @tt ColumnDataList
INSERT INTO @tt SELECT DISTINCT YEAR(訂單日期) AS 訂單年 FROM 訂貨主檔

SET @sql='SELECT 類別名稱,'+(SELECT dbo.欄位列表(@tt))
SET @sql=@sql+' FROM #Temp PIVOT(SUM(銷售量) FOR 訂單年 IN('+(SELECT dbo.欄位列表(@tt))+')) AS PP'
EXECUTE (@sql)



---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

--引伸題是做出供應商跟類別名稱的樞紐
SELECT B.供應商,C.類別名稱,COUNT(*) AS 產品數量
INTO #Temp
FROM 產品資料 AS A JOIN 供應商 AS B ON A.供應商編號=B.供應商編號 
 JOIN 產品類別 AS C ON C.類別編號=A.類別編號
GROUP BY B.供應商,C.類別名稱

DECLARE @sql NVARCHAR(MAX)
DECLARE @tt ColumnDataList
INSERT INTO @tt SELECT DISTINCT 類別名稱 FROM 產品類別

SET @sql='SELECT 供應商,'+(SELECT dbo.欄位列表(@tt))
SET @sql=@sql+' FROM #Temp PIVOT(SUM(產品數量) FOR 類別名稱 IN('+(SELECT dbo.欄位列表(@tt))+')) AS PP'
EXECUTE (@sql)

第一題結果：

第二題結果：