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 中国語 Web サイトの他の関連記事を参照してください。