How to Dynamically Pivot Data in SQL Server Using Dynamic SQL?

Dynamic pivot data in SQL Server using dynamic SQL

This article introduces how to use dynamic SQL to dynamically pivot data in SQL Server.

Question

Given a table with columns "Date", "Category" and "Amount", the goal is to convert the data into a pivot format where the categories become the columns and the dates become the rows. The converted data should look like:

<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>

Solution

In order to implement dynamic pivoting in SQL Server, you can use dynamic SQL. Here are step-by-step instructions:

1. Create temporary table: This step is not mandatory but recommended for storing data used for pivoting.
2. Build a dynamic column list: Use a SELECT statement with FOR XML PATH() to get the different category values ​​and build a comma-separated list of quoted category names.
3. Construct a dynamic query: Concatenate the column list with the required PIVOT query syntax. This query uses max() to aggregate the amount values ​​for each category and date combination.
4. Execute dynamic queries: Use the sp_executesql stored procedure to execute dynamic queries. The result will be pivoted data.

Sample code

<code class="language-sql">-- 创建临时表
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>

Results

<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>

The above is the detailed content of How to Dynamically Pivot Data in SQL Server Using Dynamic SQL?. For more information, please follow other related articles on the PHP Chinese website!