SQL Server 2008 で複数の列を使用して動的ピボットを実行するにはどうすればよいですか?

SQL Server の複数の列を使用した動的ピボット

概要

SQL Server では、複数の列にわたってデータをピボットするための限定されたオプションが提供されます。この記事では、ピボット解除と動的 SQL を組み合わせて、SQL Server 2008 で複数の列を含む動的ピボットを実現する方法について説明します。

問題の理解

目的は、複数の列を含むテーブルを変換することです。列を、列の値が列ヘッダーになる、より使いやすい形式に変換します。この場合、テーブルには次のデータが含まれます:

ID	YEAR	TYPE	TOTAL	VOLUME
DD1	2008	A	1000	10
DD1	2008	B	2000	20
DD1	2008	C	3000	30
DD1	2009	A	4000	40
DD1	2009	B	5000	50
DD1	2009	C	6000	60
DD2	2008	A	7000	70
DD2	2008	B	8000	80
DD2	2008	C	9000	90
DD2	2009	A	10000	100
DD2	2009	B	11000	110
DD2	2009	C	1200	120

必要な出力は次のとおりです:

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

解決策

データのピボット解除

複数の列をピボットするには、まずデータのピボットを解除する必要があります。これは、元の列名を識別する列列と値を含む値列を使用して、列を行に変換することを意味します。

select id, 
    col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
    value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total', total union all
    select 'volume', volume
) c (col, value);

動的 PIVOT 関数

次のステップは、 PIVOT 関数は、ピボットされていないデータを目的の形式に変換します。ただし、SQL Server には複数の列をピボットする場合の制限があります。これを克服するために、動的 SQL を使用して実行時に PIVOT クエリを構築します。

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+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, 
                    col = cast(t_year as varchar(4))+''_''+t_type+''_''+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 '

execute sp_executesql @query;

結論

アンピボットと動的 SQL を組み合わせることで、SQL の複数の列にわたって動的ピボットを実行できます。 Server 2008。これは、データをより使いやすい形式に変換するための柔軟なソリューションを提供します。

以上がSQL Server 2008 で複数の列を使用して動的ピボットを実行するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。