SQL Server 2008에서 여러 열을 사용하여 동적 피벗을 수행하는 방법은 무엇입니까?

SQL Server의 여러 열을 사용한 동적 PIVOTING

개요

SQL Server는 여러 열에 걸쳐 데이터를 피벗하는 데 제한된 옵션을 제공합니다. . 이 문서에서는 피벗 해제 SQL과 동적 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

해결책

데이터 피벗 해제

여러 열을 피벗하려면 먼저 데이터 피벗을 해제해야 합니다. 이는 원래 열 이름을 식별하는 col 열과 값을 포함하는 값 열을 사용하여 열을 행으로 변환하는 것을 의미합니다.

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!