ROW ↔ COLUMN (PIVOT)

요청이 있을때만 두드리니 실력이 늘리가 있나.. 에휴;

초보티를 벗지 못하는 내가 참 밉다.

마치기 직전에 회사에서 요청이 왔는데

피벗기능을 사용해야 하는 요청이었다.

회사의 정보를 여기서 공개 할 순 없고, 

비슷한 가상의 테이블을 만들어서 실습해보자.

까먹지 말자!!!

시나리오) 그동안 회사 직원들에게 지급했던 급여를 살펴보려 한다.

테이블명: PAYMENT_TBL

컬럼명: 사번, 급여지급월(연도-월), 금액

예시) 

사번	지급월	금액
1111	2016-01	100
1112	2016-01	120
1113	2016-01	110
1114	2016-01	100
1111	2015-12	90
1111	2015-11	100
1113	2015-12	120
1115	2015-12	200
1115	2015-10	200
1115	2015-09	200

세로로 나열돼 있는 데이터를 가로로 나열하기 위한 실습이므로, 데이터의 순서와 급여액수 등의 문제는 그냥 넘어간다. (대충 입력...)

원하는 모양) 

사번	2016-01	2015-12	2015-11	2015-10	2015-09	2015-08	2015-07
1111	100	90	100	0	0	0	0
1112	120	0	0	0	0	0	0
1113	110	120	0	0	0	0	0

방법1)

SUM() 과 CASE 문을 사용하여 원하는 모양을 만들어보자.

SELECT 

사번

, 이름

, SUM(CASE 지급월 WHEN '2015-11' THEN 금액 ELSE 0 END) AS [2015-11]

, SUM(CASE 지급월 WHEN '2015-12' THEN 금액 ELSE 0 END) AS [2015-12]

, SUM(CASE 지급월 WHEN '2016-01' THEN 금액 ELSE 0 END) AS [2016-01]

        ..... 바로 위 3줄 처럼 가로로 배열 시키고자 하는 컬럼을 같은 형식으로 만든다.

FROM

PAYMENT_TBL

GROUP BY

사번, 이름

ORDER BY

사번

위와 같이 하면 원하는 모양대로 결과가 나오긴 하지만, 보고 싶어 하는 컬럼을 위와 같이 하나하나 만들어줘야 한다.

예시에서는 몇개 되지 않는 데이터지만, 10년이 넘는 장수 기업에 직원이 300명 가량이라고 생각해보자.

현직원만 300명정도 이고, 퇴사자를 포함하면 더 많은 인원수 일것이다.

이미 퇴사한 사람의 급여 내역을 뭐하러 보냐 라고 하면 할 말은 없지만, 

각 데이터를 DB 에 저장하는 이유가 필요할때 보기 위해서 저장하는 것이니 상황을 완전 배제하진 말자.

설명)

고정이 될 컬럼을 지정하고(사번, 이름), 

그 다음 순서 부터는 컬럼명이 급여의 지급월이 되고, 컬럼의 내용은 금액이 표시되도록 한다.

지급월이 '2015-11' 이면 금액을 더하기 한다라고 풀이 할 수 있겠다.

두번째 세번째도 마찬가지로 

지정된 지급월이 맞으면 해당 금액을 SUM() 한다. 

그럴리는 잘 없지만 한달에 두번 급여를 받았다면 두번의 급여가 합산되서 표시 되겠지?

SUM을 사용했으니 GROUP BY 로 묶어주자.

원리를 알면 그리 어렵지 않은데, 백지상태에서는 쉽사리 생각나지 않는다.

에휴....

다음은 MSSQL 에서 제공하는 PIVOT(피벗) 쿼리를 사용해보자.

방법2)

방법1) 과 비슷하지만 조금 다르다.

SELECT

사번

, 이름

  , [2015-11]

  , [2015-12]

  , [2016-01]

  ..... 바로 위 3줄 처럼 가로로 배열 시키고자 하는 컬럼을 같은 형식으로 만든다.

FROM

(

        SELECT

            사번

            , 이름

            , 금액

            , 지급월

        FROM

            PAYMENT_TBL

) A

PIVOT

    (

        SUM(금액)

        FOR 지급월 IN ([2015-11], [2015-12], [2016-01] ..... 위에 지정한 컬럼을 그대로 적어주자.)

    ) B

설명)

먼저 SELECT로 외형을 잡아주자(?)

사번과 이름 그 다음 각 지급월을 컬럼명으로 하고, 컬럼 내용은 금액이 들어가겠지?

제일 상단의 외형을 만드는 SELECT 에서 사용할 컬럼을 모두 여기에 적어줘야 한다. (어찌보면 당연하네..)

그리고 PIVOT 이라는 키워드를 사용해서 위에서 SELECT 한 테이블을 돌려버리는 듯 하다(?)

(이렇게 사용하니까 되긴 된다만, 완벽하게 이해가 가지 않는다. 좀 더 찾아봐야 할 듯 하다.)

아직 생소한 부분이 있다.

FOR 라는 키워드와 PIVOT,

FOR 뒤에 나오는 컬럼명과 IN 뒤에 나열되는 컬럼들 과의 관계 등등

좀 더 찾아봐야겠다.

참고: http://lab.cliel.com/entry/SQL-%ED%94%BC%EB%B2%97Pivot%EA%B3%BC-%EC%96%B8%ED%94%BC%EB%B2%97UnPivot