如何使用动态 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存储过程执行动态查询。结果将是透视数据。

示例代码

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

结果

<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中文网其他相关文章！