如何使用動態 SQL 在 SQL Server 中動態透視資料？

使用動態SQL在SQL Server中動態透視資料

本文介紹如何使用動態SQL在SQL Server中動態透視資料。

問題

給定一個包含「日期」、「類別」和「金額」列的表，目標是將資料轉換為透視格式，其中類別成為列，日期成為行。轉換後的資料應類似：

<code>日期        ABC             DEF             GHI
1/1/2012    1000.00
2/1/2012                    500.00
2/1/2012                                    800.00
2/10/2012                   700.00
3/1/2012    1100.00</code>

解

為了在SQL Server中實現動態透視，可以使用動態SQL。以下是逐步說明：

1. 建立臨時表：此步驟不是強制性的，但建議用於儲存用於透視的資料。
2. 建立動態列清單：使用FOR XML PATH()的SELECT語句取得不同的類別值，並建立一個以逗號分隔的帶引號的類別名稱清單。
3. 建構動態查詢：將欄位清單與所需的PIVOT查詢語法連結。此查詢使用max()聚合每個類別和日期組合的金額值。
4. 執行動態查詢：使用sp_executesql預存程序執行動態查詢。結果將是透視數據。

範例程式碼

-- 创建临时表
CREATE TABLE temp (
    date DATETIME,
    category VARCHAR(3),
    amount MONEY
);

-- 插入示例数据
INSERT INTO temp VALUES ('1/1/2012', 'ABC', 1000.00);
INSERT INTO temp VALUES ('2/1/2012', 'DEF', 500.00);
INSERT INTO temp VALUES ('2/1/2012', 'GHI', 800.00);
INSERT INTO temp VALUES ('2/10/2012', 'DEF', 700.00);
INSERT INTO temp VALUES ('3/1/2012', 'ABC', 1100.00);


-- 构建动态列列表
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- 构造动态查询
SET @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p ';


-- 执行动态查询
EXECUTE(@query);

-- 删除临时表
DROP TABLE temp;

結果

<code>日期                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL</code>

以上是如何使用動態 SQL 在 SQL Server 中動態透視資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！