[펌] SQL 세로를 가로로 조회(ORACLE)

프로그래밍을 하다보면 SQL을 통해서 자료를 가져올때 만나는 당황스러움중에 한가지가 세로로 어떤 내용을 보여주고자 할 경우이다. 언어차원에서 해결을 해결 할 경우도 있지만(개인적으로 언어차원의 구현이 쉽다고 본다. 단, 무한정 동적인 경우에 해당한다) SQL로 해결을 해야되는 경우가 있다.

예를 들자면 이런 경우

홍길동 수학 100
홍길동 국어 100
홍길동 영어 100
김철수 수학 90
김철수 수학 80
김철수 수학 70

이렇게 표시된 데이터를
이름 수학 국어 영어 합계 평균
홍길동 100 100 100 300 100
김철수 90 80 70 240 80

이런 경우라고 할 수 있다.

가급적이면 이런 경우 기본이 되는 데이터를 긁는 SQL보다 모양을 맞추기 위한 이런 쿼리가 인라인뷰로 덕지덕지붙는 경향이 있어서 프로그램에서 잘 만들어진 라이브러리로 처리하는 것을 선호하는 편인데…

이노무 웹프로그램은 출력물을 별도의 출력물 프로그램(어플)없이 짤 방도가 묘연하기 때문에 출력물을 위한 레포팅 프로그램을 이용해야 하며 이 레포팅 프로그램을 이용할 경우 프로그래밍은 개입을 할 수 없는 경우가 절대다수이다.

나에게 주어진 문제도 QR코드를 출력해주기 위해서 출력물을 짜줘야되는데 Reprot Designer라는 툴을 고객사에서 이용하기때문에 프로그래밍으로 해결할 방법이 없었다. 결국 출력물에 바로 실어보낼 수 밖에 없는 SQL으로 처리를 해야되는데 QR코드 출력이 일반 바코드 출력프린터가 아니라 라벨지를 시용하기 때문에 1줄에 4개의 QR코드가 찍혀야 했다.(물론 모두 다른 QR코드이다.)

그래서 억지로 세로데이터를 가로로 4개씩 불러들어야 하는 상황이였다. 이를 풀다가 오라클의 pivot이라는 기능도 알게 되고 해서 남겨둔다.

쉽게 쉽게 핸들링하기 위해서 1~12까지 데이터를 이용해서 1줄에 4개씩 표현한다고 가정하고 기본데이터를 준비하면 이렇다.

1 2 3 4 5

WITH TEMP_TABLE AS (   SELECT LEVEL AS ROW_NUM, LEVEL * 10 AS ROW_VALUE     FROM DUAL CONNECT BY LEVEL - 1 < 12 ) SELECT * FROM TEMP_TABLE

결과는 아래와 같다.

이 데이터를 이용해서 세로로 12개가 나오는 데이터를(순차적으로) 1줄에 4개씩 표현을 하기 위해서 생각을 데이터를 분할하자면 ROW_NUM이라는 컬럼의 데이터를 이용할 수 잇다. 더불어 옆으로 펼칠작업까지 하기 위해서 나머지 값까지 구하면 아래와 같다.

1 2 3 4 5 6

WITH TEMP_TABLE AS (   SELECT LEVEL AS ROW_NUM, LEVEL * 10 AS ROW_VALUE     FROM DUAL CONNECT BY LEVEL - 1 < 12 ) SELECT T.*, CEIL(T.ROW_NUM/4) AS ROW_CEIL, MOD(T.ROW_NUM,4) AS ROW_MOD   FROM TEMP_TABLE T

결과는 아래와 같다.

여기에 더불어 나머지연산자를 이용하여 표시해야될 데이터의 컬럼을 표시하게 되자면 아래와 같아진다.

1 2 3 4 5 6 7 8 9 10

WITH TEMP_TABLE AS (   SELECT LEVEL AS ROW_NUM, LEVEL * 10 AS ROW_VALUE     FROM DUAL CONNECT BY LEVEL - 1 < 12 ) SELECT T.*, CEIL(T.ROW_NUM/4) AS ROW_CEIL, MOD(T.ROW_NUM,4) AS ROW_MOD, DECODE(MOD(ROW_NUM,4), 1, ROW_VALUE) AS VALUE1, DECODE(MOD(ROW_NUM,4), 2, ROW_VALUE) AS VALUE2, DECODE(MOD(ROW_NUM,4), 3, ROW_VALUE) AS VALUE3, DECODE(MOD(ROW_NUM,4), 0, ROW_VALUE) AS VALUE4   FROM TEMP_TABLE T

결과는 아래와 같다.

이렇게 표현된 데이터를 null값을 무시하고 ROW_CEIL값(CEIL함수를 이용한 데이터를 이용하여) 집계만 내주면 되겠다.

1 2 3 4 5 6 7 8 9 10 11

WITH TEMP_TABLE AS (   SELECT LEVEL AS ROW_NUM, LEVEL * 10 AS ROW_VALUE     FROM DUAL CONNECT BY LEVEL - 1 < 12 ) SELECT CEIL(T.ROW_NUM/4) AS ROW_CEIL, MIN(DECODE(MOD(ROW_NUM,4), 1, ROW_VALUE)) AS VALUE1, MIN(DECODE(MOD(ROW_NUM,4), 2, ROW_VALUE)) AS VALUE2, MIN(DECODE(MOD(ROW_NUM,4), 3, ROW_VALUE)) AS VALUE3, MIN(DECODE(MOD(ROW_NUM,4), 0, ROW_VALUE)) AS VALUE4   FROM TEMP_TABLE T GROUP BY CEIL(T.ROW_NUM/4)

결과는 아래와 같다.

상기 표현방법은 DECODE 대신 CASE를 이용한다면 다른 DB에서요 사용할 수 있을 것이다.

상기와 결과는 같지만 다르게 표현할 수 있는 오라클의 함수도 제공되고 있다. 이름 하여 PIVOT….

1 2 3 4 5 6 7 8 9 10

WITH TEMP_TABLE AS (   SELECT LEVEL AS ROW_NUM, LEVEL * 10 AS ROW_VALUE     FROM DUAL CONNECT BY LEVEL - 1 < 12 ) SELECT ROW_CEIL, MIN(VALUE1), MIN(VALUE2), MIN(VALUE3), MIN(VALUE4)   FROM (SELECT T.*, CEIL(T.ROW_NUM/4) AS ROW_CEIL, MOD(T.ROW_NUM,4) AS ROW_MOD    FROM TEMP_TABLE T)   PIVOT ( MIN(ROW_VALUE) FOR ROW_MOD IN (1 VALUE1, 2 VALUE2, 3 VALUE3, 0 VALUE4) ) GROUP BY ROW_CEIL         ORDER BY ROW_CEIL

앞서 MIN(DECODE(MOD(ROW_NUM,4), 1, ROW_VALUE)) AS VALUE1 처리 되던 부분이 분할되었다.

1. PIVOT ( MIN(ROW_VALUE) FOR ROW_MOD IN (1 VALUE1, 2 VALUE2, 3 VALUE3, 0 VALUE4) 을 통하여 ROW_VALUE를 MIN으로 집계
2. ROW_MOD 값이 1,2,3,0에 따라서 VALUE1~VALUE4 컬럼에 표시하도록
3. 실제 값은 SELECT ROW_CEIL, MIN(VALUE1), MIN(VALUE2), MIN(VALUE3), MIN(VALUE4) 에서 집계를 하고 GROUP BY ROW_CEIL로 집계기준을 마련하게 된다.

참고)
CEIL([평가식]) : 평가식의 값보다 크거나 같은 정수의 최소값 (올림 함수)
FLOOR([평가식]) : 평가식의 값보다 작거나 같은 정수의 최대값 (버림 함수)

출처 : SQL 세로를 가로로 조회(ORACLE)