如何在 SQL Server 中动态透视多个列？

SQL Server 中多列的动态透视转换

概述

本文解决了在 Microsoft 中对多列执行动态透视操作的挑战SQL 服务器。目标是将数据转换为数据透视表格式，其中行代表指定列中的不同值，列代表其他列中的值。

取消数据透视

在执行透视操作之前，数据必须取消透视。此过程通过将多列转换为行来重塑数据。在这种情况下，Total 和 Volume 列将被取消透视。

SELECT ID, 
    CONCAT(T_YEAR, '_', T_TYPE, '_', COL) AS COL, 
    VALUE
FROM ATM_TRANSACTIONS t
CROSS APPLY
(
    SELECT 'total', TOTAL UNION ALL
    SELECT 'volume', VOLUME
) c (COL, VALUE)

生成动态数据透视查询

数据取消透视后，就可以构建动态透视查询。此查询根据未透视数据动态构建数据透视表所需的列。

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(T_YEAR, '_', T_TYPE, '_', COL)) 
                    FROM ATM_TRANSACTIONS t
                    CROSS APPLY
                    (
                        SELECT 'total', 1 UNION ALL
                        SELECT 'volume', 2
                    ) c (COL, SO)
                    GROUP BY COL, SO, T_TYPE, T_YEAR
                    ORDER BY T_YEAR, T_TYPE, SO
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'SELECT ID,' + @cols + ' 
            FROM 
            (
                SELECT ID, 
                    CONCAT(T_YEAR, ''_''+T_TYPE+''_''+COL) AS COL, 
                    VALUE
                FROM ATM_TRANSACTIONS t
                CROSS APPLY
                (
                    SELECT ''total'', TOTAL UNION ALL
                    SELECT ''volume'', VOLUME
                ) c (COL, VALUE)
            ) x
            PIVOT 
            (
                MAX(VALUE)
                FOR COL IN (' + @cols + ')
            ) p ';

执行动态查询

动态查询是使用 sp_executesql 存储过程执行的。此过程获取动态 SQL 代码生成的查询并执行它。

EXECUTE SP_EXECUTESQL @QUERY;

结果

动态数据透视操作的输出是一个数据透视表，其行和列具有不同的 ID 值表示 T_YEAR 和 T_TYPE 的每个组合的未透视总计和交易量值。结果如下：

ID	2008_A_TOTAL	2008_A_VOLUME	2008_B_TOTAL	2008_B_VOLUME	2008_C_TOTAL	2008_C_VOLUME	2009_A_TOTAL	2009_A_VOLUME	2009_B_TOTAL	2009_B_VOLUME	2009_C_TOTAL	2009_C_VOLUME
DD1	1000	10	2000	20	3000	30	4000	40	5000	50	6000	60
DD2	7000	70	8000	80	9000	90	10000	100	11000	110	12000	120

以上是如何在 SQL Server 中动态透视多个列？的详细内容。更多信息请关注PHP中文网其他相关文章！