ROW 펼치기 팁 - 쿼리로 행(세로)을 열(가로)로 펼치는 방법

쿼리를 짜다보면 쿼리 결과를 가로로 만들기 위해서 피벗을 사용해야 할 경우가 있다.

물론 단순한 SELECT, UPDATE, DELETE, INSERT만 하는 경우에는 이런 쿼리를 짤 필요가 없겠지만 

흔히 분석계 쪽에서의 활용을 위해 피벗을 해야한다면 상당히 골치아프다. 

물론 DB가 피벗을 지원한다면 배워서 사용하면 된다. 하지만 피벗 결과를 N번 다시 피벗하게 되는 경우가 있는데 그때는 머리가 아프다.

참고로 SQL SERVER는 PIVOT 함수를 지원한다. 하지만 지원하지 않는 DB가 꽤 있다.

그럴 때를 대비해서 쉽게 쿼리 세로 결과를 가로로 만드는 방법이다.

즉 행을 열로 펼치는 방법을 설명하고자 한다. (로우를 컬럼으로 만드는 방법이다)

예를 들어서 학생에게 신발 5개씩을 추천하기 위한 테이블을 생성한다.

STUDENT_ID 차원 별로 SHOES_NM이 5개씩 있는 구조이다.

// 테이블 생성

CREATE TABLE STUDENT_SHOES (

  STUDENT_ID BIGINT NULL

, SHOES_NM VARCHAR(100) NULL

) 

// 데이터 삽입

INSERT INTO STUDENT_SHOES VALUES(1,'구두');

INSERT INTO STUDENT_SHOES VALUES(1,'슬리퍼');

INSERT INTO STUDENT_SHOES VALUES(1,'나이키운동화');

INSERT INTO STUDENT_SHOES VALUES(1,'전투화');

INSERT INTO STUDENT_SHOES VALUES(1,'아디다스운동화');

INSERT INTO STUDENT_SHOES VALUES(2,'구두1');

INSERT INTO STUDENT_SHOES VALUES(2,'슬리퍼1');

INSERT INTO STUDENT_SHOES VALUES(2,'나이키운동화1');

INSERT INTO STUDENT_SHOES VALUES(2,'전투화1');

INSERT INTO STUDENT_SHOES VALUES(2,'아디다스운동화1');

// 조회

SELECT * FROM STUDENT_SHOES

이 결과를 가로로 펼쳐보자.

SQL SERVER 쿼리 기준이며 방법은 다음과 같다.

SELECT   STUDENT_ID = STUDENT_ID

       , SHOES_NM_1 = MAX(CASE WHEN NUM = 1 THEN SHOES_NM END)

   , SHOES_NM_2 = MAX(CASE WHEN NUM = 2 THEN SHOES_NM END)

   , SHOES_NM_3 = MAX(CASE WHEN NUM = 3 THEN SHOES_NM END)

   , SHOES_NM_4 = MAX(CASE WHEN NUM = 4 THEN SHOES_NM END)

   , SHOES_NM_5 = MAX(CASE WHEN NUM = 5 THEN SHOES_NM END)

FROM (

        SELECT  STUDENT_ID

              , SHOES_NM

          , ROW_NUMBER() OVER(PARTITION BY STUDENT_ID ORDER BY SHOES_NM) NUM

        FROM STUDENT_SHOES

     ) A

GROUP BY STUDENT_ID

몇 가지 더 이야기를 한다면 현재 원천 테이블(STUDENT_SHOES)에 차원이 STUDENT_ID 하나인데 

예를 들어서 계절추천용(여름/겨울) 컬럼(SEASON)을 하나 더 만들고 여름(10) 5개, 겨울(20) 5개씩 추천을 한다면 다음처럼 하면 된다.

// 계절 컬럼 추가

ALTER TABLE STUDENT_SHOES ADD SEASON INT NULL;

// 데이터 삭제

TRUNCATE TABLE STUDENT_SHOES;

// 데이터 재 삽입

INSERT INTO STUDENT_SHOES VALUES(1,'여름 구두',1);

INSERT INTO STUDENT_SHOES VALUES(1,'여름 슬리퍼',1);

INSERT INTO STUDENT_SHOES VALUES(1,'여름 나이키운동화',1);

INSERT INTO STUDENT_SHOES VALUES(1,'여름 전투화',1);

INSERT INTO STUDENT_SHOES VALUES(1,'여름 아디다스운동화',1);

INSERT INTO STUDENT_SHOES VALUES(1,'겨울 A구두',2);

INSERT INTO STUDENT_SHOES VALUES(1,'겨울 B슬리퍼',2);

INSERT INTO STUDENT_SHOES VALUES(1,'겨울 C나이키운동화',2);

INSERT INTO STUDENT_SHOES VALUES(1,'겨울 D전투화',2);

INSERT INTO STUDENT_SHOES VALUES(1,'겨울 E아디다스운동화',2);

INSERT INTO STUDENT_SHOES VALUES(2,'여름 Q구두',1);

INSERT INTO STUDENT_SHOES VALUES(2,'여름 W슬리퍼',1);

INSERT INTO STUDENT_SHOES VALUES(2,'여름 E나이키운동화',1);

INSERT INTO STUDENT_SHOES VALUES(2,'여름 R전투화',1);

INSERT INTO STUDENT_SHOES VALUES(2,'여름 G아디다스운동화',1);

INSERT INTO STUDENT_SHOES VALUES(2,'겨울 T구두',2);

INSERT INTO STUDENT_SHOES VALUES(2,'겨울 Y슬리퍼',2);

INSERT INTO STUDENT_SHOES VALUES(2,'겨울 U나이키운동화',2);

INSERT INTO STUDENT_SHOES VALUES(2,'겨울 I전투화',2);

INSERT INTO STUDENT_SHOES VALUES(2,'겨울 O아디다스운동화',2);

// 조회

SELECT * FROM STUDENT_SHOES

// 학생 및 계절별로 신발 추천하기

SELECT   STUDENT_ID  = STUDENT_ID

       , SHOES_NM_1  = MAX(CASE WHEN NUM = 1 AND SEASON = 1 THEN SHOES_NM END)

   , SHOES_NM_2  = MAX(CASE WHEN NUM = 2 AND SEASON = 1 THEN SHOES_NM END)

   , SHOES_NM_3  = MAX(CASE WHEN NUM = 3 AND SEASON = 1 THEN SHOES_NM END)

   , SHOES_NM_4  = MAX(CASE WHEN NUM = 4 AND SEASON = 1 THEN SHOES_NM END)

   , SHOES_NM_5  = MAX(CASE WHEN NUM = 5 AND SEASON = 1 THEN SHOES_NM END)

   , SHOES_NM_6  = MAX(CASE WHEN NUM = 1 AND SEASON = 2 THEN SHOES_NM END)

   , SHOES_NM_7  = MAX(CASE WHEN NUM = 2 AND SEASON = 2 THEN SHOES_NM END)

   , SHOES_NM_8  = MAX(CASE WHEN NUM = 3 AND SEASON = 2 THEN SHOES_NM END)

   , SHOES_NM_9  = MAX(CASE WHEN NUM = 4 AND SEASON = 2 THEN SHOES_NM END)

   , SHOES_NM_10 = MAX(CASE WHEN NUM = 5 AND SEASON = 2 THEN SHOES_NM END)

FROM (

        SELECT  STUDENT_ID

              , SHOES_NM

  , SEASON

          , ROW_NUMBER() OVER(PARTITION BY STUDENT_ID, SEASON ORDER BY SHOES_NM) NUM

        FROM STUDENT_SHOES

     ) A

GROUP BY STUDENT_ID

이상으로 로우(ROW)를 컬럼(COLUMN)으로 펼치는 팁 마칩니다.