之前有和大家分享使用CASE WHEN 搭配聚合函數實現樞紐分析
但今天如果我們要轉換成行的列希望是動態依照目前資料庫的欄位要處理呢?
我們可以使用Dynamic pivot
Dynamic pivot 核心概念其實是把我們要使用的 pivot SQL語法動態產生出來
話不多說先附上 程式碼
CREATE TABLE T(
userName VARCHAR(100),
Price int,
Dt DATE
);
INSERT INTO T VALUES ('Tom',100,'2017-01-01');
INSERT INTO T VALUES ('Amy',200,'2017-01-02');
INSERT INTO T VALUES ('Tom',1311,'2017-01-03');
INSERT INTO T VALUES ('Tom',122,'2017-03-01');
INSERT INTO T VALUES ('Tom',111,'2017-04-01');
INSERT INTO T VALUES ('Amy',232,'2017-05-01');
INSERT INTO T VALUES ('Tom',2312,'2017-05-02');
INSERT INTO T VALUES ('Tom',23,'2017-05-03');
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',SUM(CASE WHEN Dt = '''+ CAST(Dt AS VARCHAR(10)) +''' THEN Price ELSE 0 END) AS ' + QUOTENAME(Dt)
FROM T
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT userName,'+@cols+' FROM T GROUP BY userName' ;
EXECUTE(@query)
因為範例我們使用 SQL SERVER
所以使用 FOR XML PATH 語法將我們 CASE WHEN pivot SQL 語法產生並把他附值給 @cols 變數
SET @cols = STUFF((SELECT distinct ',SUM(CASE WHEN Dt = '''+ CAST(Dt AS VARCHAR(10)) +''' THEN Price ELSE 0 END) AS ' + QUOTENAME(Dt)
FROM T
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,'')
因為 Dt 行會有重複的值,所以 distinct 來過濾
這會產生如下的SQL語法
,SUM(CASE WHEN Dt = '2017-01-01' THEN Price ELSE 0 END) AS [2017-01-01],SUM(CASE WHEN Dt = '2017-01-02' THEN Price ELSE 0 END) AS [2017-01-02],SUM(CASE WHEN Dt = '2017-01-03' THEN Price ELSE 0 END) AS [2017-01-03],SUM(CASE WHEN Dt = '2017-03-01' THEN Price ELSE 0 END) AS [2017-03-01],SUM(CASE WHEN Dt = '2017-04-01' THEN Price ELSE 0 END) AS [2017-04-01],SUM(CASE WHEN Dt = '2017-05-01' THEN Price ELSE 0 END) AS [2017-05-01],SUM(CASE WHEN Dt = '2017-05-02' THEN Price ELSE 0 END) AS [2017-05-02],SUM(CASE WHEN Dt = '2017-05-03' THEN Price ELSE 0 END) AS [2017-05-03]
在使用 STUFF 將第一個 , 給移除掉
有了上面CASE WHEN pivot SQL 語法,最後只需把剩下要用到Table sql語句給組出來在使用 EXECUTE 動態呼叫SQL語法
如果本文對您幫助很大，可街口支付斗內鼓勵石頭^^
