(SQL Server)Dynamic pivot 動態樞紐分析

前言：

之前有和大家分享使用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 @SQL = 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 sp_executesql @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 將第一個 , 給移除掉

sqlfiddle

最後在把要使用的表和前面組的Pivot query串起來.

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' ;
SELECT @query;

最後產生：

SELECT userName,
    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]
FROM T 
GROUP BY userName

有了上面CASE WHEN pivot SQL 語法,最後只需把剩下要用到Table sql語句給組出來在使用 EXECUTE sp_executesql 動態呼叫SQL語法

sqlfiddle

__此文作者__：Daniel Shih(石頭)
__此文地址__： https://isdaniel.github.io/dynamic-pivot/
__版權聲明__：本博客所有文章除特別聲明外，均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處！

🚫 Ad Blocker Detected

前言：

程式碼