SQL 서버 : 5 열 이상 동적 피벗

여러 개의 열이 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   12000   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             12000           120

테이블을 생성하기 위해 다음과 같이 내 SQL 서버 2008 쿼리는 다음과 같습니다

CREATE TABLE ATM_TRANSACTIONS 
(
 ID varchar(5),
 T_YEAR varchar(4),
 T_TYPE varchar(3), 
 TOTAL int,
 VOLUME int
);

INSERT INTO ATM_TRANSACTIONS
(ID,T_YEAR,T_TYPE,TOTAL,VOLUME)

VALUES
('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);

나는 확실하지 내가 동적 코드와 SQL Server의 PIVOT 기능의 조합을 사용할 수 있는지 해요 그래서, T_Year 열은 향후 변경 될 수 있지만, T_TYPE 열은 일반적으로 알고있다?

나는 여기에 예를 들어 다음과 같은 시도 :

http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

하지만 난 이상한 결과로 끝났다.

해결법

==============================

1.결과를 얻기 위해서는 먼저 최종 결과를 얻기 위해 PIVOT 기능을 적용하기 전에 총 데이터 및 볼륨 열을 unpivoting 볼 필요가있을 것이다. 나의 제안은 쿼리의 하드 코딩 된 버전은 다음 동적 SQL로 변환 먼저 작성하는 것입니다.

결과를 얻기 위해서는 먼저 최종 결과를 얻기 위해 PIVOT 기능을 적용하기 전에 총 데이터 및 볼륨 열을 unpivoting 볼 필요가있을 것이다. 나의 제안은 쿼리의 하드 코딩 된 버전은 다음 동적 SQL로 변환 먼저 작성하는 것입니다.

UNPIVOT 과정은 행으로 이러한 여러 열을 변환합니다. UNPIVOT에 몇 가지 방법이 있습니다, 당신은 UNPIVOT 기능을 사용하거나 CROSS을 적용 할 수 있습니다. 데이터를 피벗 해제하는 코드는 유사합니다 :

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

이렇게하면 형식의 데이터를 제공합니다 :

+-----+---------------+-------+
| id  |      col      | value |
+-----+---------------+-------+
| DD1 | 2008_A_total  |  1000 |
| DD1 | 2008_A_volume |    10 |
| DD1 | 2008_B_total  |  2000 |
| DD1 | 2008_B_volume |    20 |
| DD1 | 2008_C_total  |  3000 |
| DD1 | 2008_C_volume |    30 |
+-----+---------------+-------+

그럼 당신은 PIVOT 기능을 적용 할 수 있습니다 :

select 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]
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)
) d
pivot
(
    max(value)
    for col in ([2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
                [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume])
) piv;

이제 올바른 논리를 가지고, 당신은 동적 SQL이 변환 할 수 있습니다 :

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;

이것은 당신에게 결과를 줄 것이다 :

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

==============================

2.

declare @stmt nvarchar(max)

select @stmt = isnull(@stmt + ', ', '') + 
        'sum(case when T_YEAR = ''' + T.T_YEAR + ''' and T_TYPE = ''' + T.T_TYPE + ''' then TOTAL else 0 end) as ' + quotename(T.T_YEAR + '_' +  T.T_TYPE + '_TOTAL') + ',' +
        'sum(case when T_YEAR = ''' + T.T_YEAR + ''' and T_TYPE = ''' + T.T_TYPE + ''' then VOLUME else 0 end) as ' + quotename(T.T_YEAR + '_' +  T.T_TYPE + '_VOLUME')
from (select distinct T_YEAR, T_TYPE from ATM_TRANSACTIONS) as T
order by T_YEAR, T_TYPE

select @stmt = '
    select
        ID, ' + @stmt + ' from ATM_TRANSACTIONS group by ID'

exec sp_executesql
    @stmt = @stmt

내가 당신을 위해 예제를 만들 수 없습니다, 그래서 불행하게도, sqlfiddle.com는 순간에 작동하지 않습니다.

동적 SQL에 의해 생성 된 쿼리는 다음과 같습니다

select
    ID,
    sum(case when T_YEAR = '2008' and T_TYPE = 'A' then TOTAL else 0 end) as 2008_A_TOTAL,
    sum(case when T_YEAR = '2008' and T_TYPE = 'A' then VOLUME else 0 end) as 2008_A_VOLUME,
    ...
from ATM_TRANSACTIONS
group by ID

==============================

3.시도하십시오 :

시도하십시오 :

DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX)

SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(T_YEAR+'_'+T_TYPE+'_TOTAL')+','+QUOTENAME(T_YEAR+'_'+T_TYPE+'_VOLUME') from ATM_TRANSACTIONS GROUP BY T_YEAR, T_TYPE

IF ISNULL(@pivv, '')<>''
    SET @Query='SELECT * FROM(
        SELECT ID, T_YEAR+''_''+T_TYPE+''_TOTAL'' TYP, TOTAL VAL from ATM_TRANSACTIONS UNION
        SELECT ID, T_YEAR+''_''+T_TYPE+''_VOLUME'' TYP, VOLUME VAL from ATM_TRANSACTIONS 
        )x pivot (SUM(VAL) for TYP in ('+@pivv+')) as xx'

IF ISNULL(@Query, '')<>''
    EXEC (@Query)

from https://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] DB 설계는 하위 유형을 사용하거나하지 않으려면? (0)	2020.04.12
[SQL] 드롭 다운리스트 데이터 소스 (0)	2020.04.12
[SQL] 쉼표는 MySQL의 "IN"절에 값을 분리 (0)	2020.04.12
[SQL] 잠시 / 루프를 수행하면 10 개 임의 결과를 얻을 수 있습니다 (0)	2020.04.12
[SQL] 테이블에서 중복 행을 삭제 (0)	2020.04.12

복붙노트

[SQL] SQL 서버 : 5 열 이상 동적 피벗

SQL 서버 : 5 열 이상 동적 피벗

해결법

1.결과를 얻기 위해서는 먼저 최종 결과를 얻기 위해 PIVOT 기능을 적용하기 전에 총 데이터 및 볼륨 열을 unpivoting 볼 필요가있을 것이다. 나의 제안은 쿼리의 하드 코딩 된 버전은 다음 동적 SQL로 변환 먼저 작성하는 것입니다.

2.

3.시도하십시오 :

'SQL' 카테고리의 다른 글

티스토리툴바