기본 강좌 32 - 피벗 변환, 피벗 해제 변환

피벗 변환

 

피벗 변환을 설명하기 전에 우선 피벗 연산에 대해 살펴보겠습니다.

 

아이디	성별	연령대	금액
AAA	남	20~24세	100
BBB	남	25~29세	50
CCC	여	35~39세	30
DDD	여	25~29세	60
EEE	여	30~34세	150

 

위와 같은 데이터에 대해 다음과 같은 형태로 집계할 수 있습니다.

또는 반대로, 다음과 같은 형태로 집계를 할 수도 있습니다.

엑셀에서는 위의 데이터를 이용하여 쉽게 피벗을 구현할 수 있습니다. 하지만, SQL 쿼리를 이용하는 경우에는 다음과 같은 형태의 복잡한 쿼리를 이용해야 합니다.

 

             SELECT [성별],

                           SUM(CASE WHEN [연령대] = ‘20~24세’ THEN [금액] ELSE 0 END) AS [20~24세],

                           SUM (CASE WHEN [연령대] = ‘25~29세’ THEN [금액] ELSE 0 END) AS [25~29세],

                           SUM (CASE WHEN [연령대] = ‘30~34세’ THEN [금액] ELSE 0 END) AS [30~34세],

                           SUM (CASE WHEN [연령대] = ‘35~39세’ THEN [금액] ELSE 0 END) AS [35~39세]

             FROM [테이블]

             GROUP BY [성별]

             GO

 

             SELECT [연령대],

                           SUM(CASE WHEN [성별] = ‘남’ THEN [금액] ELSE 0 END) AS [남],

                           SUM(CASE WHEN [성별] = ‘여’ THEN [금액] ELSE 0 END) AS [여]

             FROM [테이블]

             GROUP BY [연령대]

             GO

 

SQL Server 2005에서는 PIVOT, UNPIVOT 연산이 추가되어 위의 경우보다는 단순하게 쿼리가 가능합니다.

 

SQL 2005 SSIS에서는 피벗 변환을 이용하여 이와 같은 기능을 구현할 수 있습니다. 물론, SQL Server의 테이블에 저장된 데이터뿐만 아니라 어떠한 형태의 입력 데이터에 대해서도 피벗 구현이 가능합니다.

 

 

피벗 변환은 기본적으로 고급 피벗 편집기에서 속성을 설정하게 됩니다. 입력 열 탭에서는 입력 데이터에서 피벗 연산에 쓰일 열을 선택합니다.

 

입/출력 속성 탭의 설정을 설명하기 전에 다음과 같은 사항을 다시 명확히 설명하겠습니다.

피벗 변환에서는 입력 데이터의 열에 대해 피벗 결과의 용도를 설정합니다. 위의 입력 데이터에 대해 다음과 같은 형태의 피벗 결과를 생성한다고 할 때, [연령대] 열이 행 속성이 되는 열이 됩니다. 그리고, [성별] 열의 [남], [여]의 값이 열 속성이 되며, [금액]이 값 속성이 됩니다.

 

 

 

입력 열의 각 열에 대해 PivotUsage 값을 지정합니다.

            

PivotUsage 값	설명
0	열이 피벗 연산에 참여하지 않고 바로 출력됩니다.
1	행 속성을 가지는 열입니다.
2	이 열의 값이 열 속성을 가지게 됩니다.
3	값 속성을 가지는 열입니다.

 

피벗 기본 출력 부분에서는 행 속성을 가지는 열 및 열 속성을 가지는 열을 생성시키며, 입력 열과의 연결을 설정합니다. 자세한 사항은 아래의 따라하기를 참고하시기 바랍니다.

 

 

ú           ComparisonFlags – 그룹핑 작업을 수행할 때 문자열에 대한 비교 처리 방법입니다. 대/소문자 구분이나 문자 너비, 기호 무시 등의 문자열 비교 속성을 설정할 수 있습니다.

ú           PivotKeyValue – SQL 쿼리의 [ CASE WHEN 컬럼명 = ‘조건값’  THEN …] 형태에서 ‘조건값’ 에 해당하는 값을 지정합니다. 위의 그림에서는 출력 열 [여자]는 PivotKeyValue에서 지정한 ‘여’라는 값일 경우, 연산을 수행하는 것을 의미합니다.

ú           SourceColumn – 연산을 수행할 값의 LineageID(계보 ID) 값입니다. 본 그림에서는 금액 열의 속성 중 LineageID 값을 나타냅니다. 이 값을 지정하게 되면 자동으로 데이터 형식 속성의 DataType 속성이 변경됩니다.

 

 

참고 – 엑셀의 피벗 연산과 SSIS의 피벗 변환과의 차이점

엑셀을 이용한 피벗 연산이나 CASE WHEN 문을 이용하는 SQL 쿼리를 이용한 결과와 SSIS의 피벗 변환 결과에는 다음과 같은 차이점이 있습니다.

예제 데이터를 이용해서 설명 하겠습니다.

 

아이디	성별	연령	값
User1	남	30세	1
User2	여	25세	1
User3	남	25세	1

 

위의 데이터와 같이, 피벗에서 사용할 열과 행의 값이 고유한 경우는 출력 결과가 모두 동일합니다.

 

성별	25세	30세
남	1	1
여	1

 

 

하지만, 일반적으로 동일한 피벗 속성의 여러 값이 존재합니다.

 

아이디	성별	연령	값
User1	남	30세	1
User2	여	25세	1
User3	남	25세	1
User4	남	25세	1
User5	여	25세	1

 

이와 같은 경우, 엑셀 또는 SQL 쿼리를 이용한 피벗 결과는 다음과 같은 형태입니다.

 

성별	25세	30세
남	2	1
여	2

 

하지만, SSIS의 피벗 변환은 이와는 다른 결과가 출력됩니다.

성별	25세	30세
남	1	1
여	1
남	1
여	1

 

엑셀이나 SQL 쿼리와 같은 형태로 출력하도록 하기 위해서는 피벗 변환 후, 집계 변환을 이용하여 GROUP BY 연산을 수행해야 제대로 된 결과를 출력할 수 있습니다. 이런 속성은 SQL 2005에 새롭게 추가된 PIVOT 연산도 동일합니다.

 

 

 

피벗 해제 변환

 

피벗 해제 변환은 피벗 변환과는 반대로 피벗 형태의 출력 결과를 일반 데이터 형태로 변환하는 개체입니다.

피벗 해제 변환은 피벗 키 값에서 데이터 열로 변환될 값과 열 이름을 지정합니다.

①      입력 열 지정 – 원본 데이터에서 피벗 해제를 수행할 열을 선택합니다.

②      대상 열 – 값으로 출력될 열의 이름을 지정합니다.

③      피벗 키 값 – 피벗에서 열 속성의 열 이름을 지정합니다.

④      피벗 키 값 열 이름 - ③의 값들을 나타낼 열의 이름을 지정합니다.