by 마농 PIVOT UNPIVOT ASCII DECODE CROSS JOIN CONNECT BY LEVEL [2012.10.17]
이번 퀴즈로 배워보는 SQL 시간에는 PIVOT 및 UNPIVOT 을 이용하여 행을 열로 변환하고, 열을 행으로 변환해 조회하는 쿼리를 어떻게 작성하는지에 대해 알아본다.
지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한후 정답과 해설을 참조하길 바란다.
공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.
문제
원본테이블([표 1] 참조)에는 코드(알파벳 A~Z)과 순번(숫자 1~26)이 들어 있습니다.
[리스트 1]의 WITH문을 이용해 조회한 결과는 [표 1]의 원본 테이블과 같습니다.
[리스트 1]을 이용하여 [표 2]의 결과 테이블 자료가 조회되는 쿼리를 작성하세요.
- [리스트1] 원본 리스트
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT *
FROM t
;
- [표 1] 원본 테이블
C V ------ ---------- A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 K 11 L 12 M 13 N 14 O 15 P 16 Q 17 R 18 S 19 T 20 U 21 V 22 W 23 X 24 Y 25 Z 26
- [표 2] 결과 테이블
| GB1 | GB2 | V1 | V2 | V3 | V4 | V5 | V6 | V7 |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | A | B | C | D | E | F | G |
| 1 | 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 2 | 1 | H | I | J | K | L | M | N |
| 2 | 2 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 3 | 1 | O | P | Q | R | S | T | U |
| 3 | 2 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 4 | 1 | V | W | X | Y | Z | ||
| 4 | 2 | 22 | 23 | 24 | 25 | 26 |
문제설명
이 문제는 여러 행으로 구별되어 있는 자료를 특정 개수만큼씩 하나의 행으로 표현하는 문제입니다.
여기서는 7개씩 한 묶음으로 해서 한 줄로 표현합니다. 즉, 1번부터 7번까지의 코드값을 V1 부터 V7 까지 7개의 컬럼으로 나누어 한 줄로 표현하는 문제입니다.
그리고, 그 다음 줄에는 해당 번호를 다시 한 줄로 표현합니다.
이렇게 7개씩 끊어서 하나의 그룹으로 간주하여 구분값(1 : 코드, 2 : 번호)에 따라 두 개의 행으로 표현하는 문제입니다. 행을 구별하기 위한 구분 컬럼 두 개(GB1, GB2)를 추가로 표현했습니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트2] 정답 리스트
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT *
FROM (SELECT *
FROM (SELECT c
, TO_CHAR(v) v
, CEIL(v / 7) gb1
, MOD(v - 1, 7) + 1 gb3
FROM t
)
UNPIVOT ( v FOR gb2 IN (c AS 1, v AS 2) )
)
PIVOT ( MIN(v) FOR gb3 IN (1 v1, 2 v2, 3 v3, 4 v4, 5 v5, 6 v6, 7 v7) )
ORDER BY gb1, gb2
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
생소한 구문이 사용되었네요. 오라클 11G 버전부터 제공되는 PIVOT/UNPIVOT 기능을 이용하여 문제를 해결했습니다.
이번 퀴즈는 PIVOT/UNPIVOT에 대해 소개하는 시간입니다.
우선은, 11G 이전 버전에서는 어떤 형태로 SQL을 작성해야 했었는지 살펴보고, 비교해 봄으로써 PIVOT/UNPIVOT 이 얼마나 편리한 기능인지를 알아보도록 하겠습니다.
결과를 도출하기 위해 우선 세 가지 구분 값에 대한 정의를 내려보도록 하겠습니다.
번호 1부터 7까지를 1, 그다음 8부터 14까지를 2, 이런식으로 7개 행을 하나의 그룹으로 구별하는 식별자가 [표 2]의 GB1 항목입니다.
동일한 GB1 그룹내에서 코드행과 번호행을 구별하는 식별자가 [표 2]의 GB2 항목입니다.
마지막으로 [표 2]의 V1 부터 V7 까지 7개 항목으로 구별하는 식별자 GB3 항목이 있습니다.
GB3의 값은 1부터 7까지 차례대로 부여됩니다. 세 가지 구분값에 대한 정의가 완성되었으니 이제 구분값을 만들어 보겠습니다.
- [리스트 3] gb1, gb3
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT c
, v
, CEIL(v / 7) gb1
, MOD(v - 1, 7) + 1 gb3
FROM t
;
- [표 3] gb1, gb3 결과
C V GB1 GB3 ------ ---------- ---------- ---------- A 1 1 1 B 2 1 2 C 3 1 3 D 4 1 4 E 5 1 5 F 6 1 6 G 7 1 7 H 8 2 1 I 9 2 2 J 10 2 3 K 11 2 4 L 12 2 5 M 13 2 6 N 14 2 7 O 15 3 1 P 16 3 2 Q 17 3 3 R 18 3 4 S 19 3 5 T 20 3 6 U 21 3 7 V 22 4 1 W 23 4 2 X 24 4 3 Y 25 4 4 Z 26 4 5
[리스트 3]의 쿼리를 실행하면 [표 3]의 결과리스트가 나옵니다.
번호를 7로 나눈 뒤 CEIL 함수를 사용하여 7개 단위로 1씩 증가하는 값 GB1 을 구했습니다.
MOD 함수를 이용하여 번호를 7로 나눈 나머지 GB3 을 구했습니다.
그냥 7로 나눈 나머지를 구하면 그 값은 0~6 이 나오게 됩니다. 1~7의 값을 구하기 위해 먼저 1을 빼고 나머지를 구한 뒤 다시 1을 더하는 방법을 사용했습니다. 간단한 아이디어이지만 유용한 아이디어입니다.
그렇다면 GB2 는 어떻게 구할까요?
코드와 번호 두 개의 컬럼을 하나의 컬럼으로 합치면서 두 개의 행으로 표현해야 합니다. 거꾸로 말하면, 하나의 행이 두 개 행으로 복제가 되면서 두 개 컬럼이 하나로 합쳐지는 것이죠.
- [리스트 4] 기준 집합 만들기
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT DECODE(gb2, 1, c, v) v
, CEIL(v / 7) gb1
, gb2
, MOD(v - 1, 7) + 1 gb3
FROM t
, (SELECT LEVEL gb2 FROM dual CONNECT BY LEVEL <= 2)
ORDER BY c
;
- [표 4] 열을 행으로 변환 결과
V GB1 GB2 GB3 --- ---------- ---------- ---------- A 1 1 1 1 1 2 1 B 1 1 2 2 1 2 2 C 1 1 3 3 1 2 3 D 1 1 4 4 1 2 4 E 1 1 5 5 1 2 5 F 1 1 6 6 1 2 6 G 1 1 7 7 1 2 7 H 2 1 1 8 2 2 1 I 2 1 2 9 2 2 2 J 2 1 3 ....... 23 4 2 2 X 4 1 3 24 4 2 3 Y 4 1 4 25 4 2 4 Z 4 1 5 26 4 2 5
[리스트 4]의 쿼리를 실행하면 [표 4]의 결과리스트가 나옵니다.
두 개의 행을 가진 복제용 테이블과 조건 없이 조인하게 되면 하나의 원본테이블이 두 개로복제 됩니다.
이때 복제 전 자료와 복제 후 자료를 GB2 로 구별하며, GB2 값에 따라 DECODE 함수를 사용하여 코드값을 보여줄지 번호값을 보여줄지를 결정하게 됩니다.
여기서 사용된 방법이 바로 열을 행으로 변환하는 방법으로 오라클 11G에서 새로 등장한 UNPIVOT 을 이용한 방법으로 대체해 보도록 하겠습니다.
- [리스트 5] UNPIVOT
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT *
FROM (SELECT c
, TO_CHAR(v) v
, CEIL(v / 7) gb1
, MOD(v - 1, 7) + 1 gb3
FROM t
)
UNPIVOT ( v FOR gb2 IN (c AS 1, v AS 2) )
;
- [표 5] UNPIVOT 결과
GB1 GB3 GB2 V
---------- ---------- ---------- ---
1 1 1 A
1 1 2 1
1 2 1 B
1 2 2 2
1 3 1 C
1 3 2 3
1 4 1 D
1 4 2 4
1 5 1 E
1 5 2 5
1 6 1 F
1 6 2 6
1 7 1 G
1 7 2 7
2 1 1 H
2 1 2 8
2 2 1 I
2 2 2 9
2 3 1 J
...........
4 3 2 24
4 4 1 Y
4 4 2 25
4 5 1 Z
4 5 2 26
[리스트 5]의 쿼리를 실행하면 [표 4]의 결과와 동일한 결과가 나옵니다.
간단하게 UNPIVOT 문장을 해석해보면, c 열과 v 열 두 개의 열을 하나의 열 v 로 표현하는 것이고, 이때 c 와 v 의 행를 구별하기 위한 식별자로 1 과 2 를 별칭으로 지정한 형태입니다.
이제 이를 이용하여 행을 열로 변환하는 쿼리를 작성해 보겠습니다.
- [리스트 6] 행을 열로 전환
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT gb1, gb2
, MIN(DECODE(gb3, 1, v)) v1
, MIN(DECODE(gb3, 2, v)) v2
, MIN(DECODE(gb3, 3, v)) v3
, MIN(DECODE(gb3, 4, v)) v4
, MIN(DECODE(gb3, 5, v)) v5
, MIN(DECODE(gb3, 6, v)) v6
, MIN(DECODE(gb3, 7, v)) v7
FROM (SELECT DECODE(gb2, 1, c, v) v
, CEIL(v / 7) gb1
, gb2
, MOD(v - 1, 7) + 1 gb3
FROM t
, (SELECT LEVEL gb2 FROM dual CONNECT BY LEVEL <= 2)
)
GROUP BY gb1, gb2
ORDER BY gb1, gb2
;
리스트 의 쿼리를 [리스트 6] 실행하면 [표 2]의 결과와 동일한 결과가 나옵니다.
구분자 gb1 와 gb2 로 그룹지은 후 gb3 값에 따라 7행 1열의 형태를 1행 7열의 형태로변환하는 방법입니다.
여기서 사용된 방법이 바로 행을 열로 변환하는 방법으로 오라클 11G에서 새로 등장한 PIVOT 을 이용한 방법으로 대체해 보도록 하겠습니다.
- [리스트 7] PIVOT
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT *
FROM (SELECT *
FROM (SELECT c
, TO_CHAR(v) v
, CEIL(v / 7) gb1
, MOD(v - 1, 7) + 1 gb3
FROM t
)
UNPIVOT ( v FOR gb2 IN (c AS 1, v AS 2) )
)
PIVOT ( MIN(v) FOR gb3 IN (1 v1, 2 v2, 3 v3, 4 v4, 5 v5, 6 v6, 7 v7) )
ORDER BY gb1, gb2
;
[리스트 7]의 쿼리를 실행하면 [표 2]의 결과와 동일한 결과가 나옵니다.
간단하게 PIVOT 문장을 해석해보면, gb3 의 값 1 에 해당하는 MIN(v) 값을 별칭 v1 으로 표시하는 형태입니다. gb3 의 값 1부터 7까지가 7개의 항목 v1~v7 로 지정이 되었습니다.
이때 PIVOT 에 사용된 집합([표 4] 참조)의 전체항목(v, gb1, gb2, gb3) 중 PIVOT 구문에서 사용된 항목(v, gb3)을 제외한 나머지 항목(gb1, gb2)는 GROUP BY 항목이 되는 것입니다.
gb1, gb2로 그룹바이하고 gb3의 값에 따라 7개 항목으로 나누어지는 형태입니다.
정리해볼까요?
PIVOT 은 행을 열로 변환할 때 사용하는 방법으로 기존 GROUP BY 와 MIN, DECODE를 사용하는 방법을 대체합니다. MIN, DECODE 의 반복사용이 줄고 코드가 간결해집니다.
UNPIVOT 는 PIVOT 의 반대 개념으로 열을 행으로 변환할 때 사용하는 방법으로 기존 복제용 집합과의 CROSS JOIN 을 통한 행복제 방법과 DECODE 를 조합하는 방법을 대체합니다.
복제용 테이블과의 조인 및 DECODE 사용이 줄어 코드가 간결해집니다.
기존 방법에 익숙했던 사용자라면 오히려 기존 방법이 편하고 새로운 방법이 오히려 더 어려울 수도 있습니다. 필자 역시 PIVOT 과 UNPIVOT 을 익히는데 어려움이 좀 있었던 것을 부인하지는 않겠습니다.
PIVOT/UNPIVOT 뿐만 아니라 다른 것들도 마찬가지일 테지만, 처음 접할 때, 그 때만 좀 어려울 뿐, 그 순간이 지나고 나면 금방 익숙해 집니다.
PIVOT/UNPIVOT 사용에 익숙해진 뒤에는 확실히 새로운 방법이 훨씬 더 편리하더군요. 독자여러분들께서도 PIVOT/UNPIVOT에 도전해 보세요.
간단한 퀴즈 하나 추가로 풀어볼까요?
바로 [표 1]의 원본 테이블, 알파벳과 순번을 만들어 내는 쿼리 [리스트 1]을 만들어 보도록 하겠습니다.
먼저 26개의 순번 행을 만들어 내는 방법은 다음과 같습니다.
- [리스트 8] dual 을 이용하여 n 개의 행 만들기
SELECT LEVEL v FROM dual CONNECT BY LEVEL <= 26;
실제 데이터가 없이 dual 만으로 간단하게 만들 수 있는 아주 유용한 방법으로 다양한 용도로 사용할 수 있습니다. 알아두시면 편리하겠죠.
그 다음엔 알파벳을 순서대로 만드는 방법입니다. 이때 사용된 방법은 바로 아스키 코드값을 이용한 문자치환 방법입니다.
먼저 알파벳 'A' 와 'B' 의 아스키코드를 확인해 볼까요? ASCII 함수를 이용하면 됩니다.
- [리스트 9] 문자의 ASCII 코드 확인
SELECT ASCII('A'), ASCII('B') FROM dual;
결과 : 65, 66
이번에는 해당 아스키코드를 이용해 문자를 만들어 보겠습니다. CHR 함수를 사용합니다.
- [리스트 10] ASCII 코드를 문자로 변환
SELECT CHR(65), CHR(66) FROM dual; 결과 : A, B
이제 "CONNECT BY LEVEL <= 26" 를 이용한 행복제 방법과 CHR 함수를 이용한 문자 생성방법, 이 두가지 방법을 하나로 합쳐보면 우리가 원하는 결과를 얻을 수 있습니다.
- 강좌 URL : http://www.gurubee.net/lecture/2214
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
테스트를 할 수 없어서 아쉬워요. ㅠㅠ.....
근데, 이해하기 쪼매 힘들다는.. 걍 pivot, unpivot 구문을 외우는 방법도 하나겠죠??!!
이해하고 다시 댓글 다는걸로~~ㅎ
감사합니다. ^^
너무 자세한 설명 감사합니다 ~
너무 자세한 설명입니다. 댓글 달고 싶어서 회원가입하고 댓글남깁니다.
감사합니다.
와 진짜 설명 자세히 해주셨네요 이해가 아주 잘됐습니다. 감사합니다.
pivot, unpivot 은 모르겠고, 없는 그룹을 만들어서 나누는건 인상깊게 보고 갑니다.
pivot, unpivot 을 이제서야 이해했습니다. 작년 8월엔 이해가 안갔었는데 정말 감격스럽습니다.
WITH T AS
(
SELECT CHR(LEVEL+64) C, LEVEL V
FROM DUAL
CONNECT BY LEVEL <= 26
)
SELECT * FROM
(
SELECT CH, VAL,
CASE WHEN V<=7*1 THEN 1
WHEN V<=7*2 THEN 2
WHEN V<=7*3 THEN 3
WHEN V<=7*4 THEN 4
END GB1,
CASE WHEN CH BETWEEN 'A' AND 'Z' THEN '1'
ELSE '2'
END GB2
FROM
(SELECT C AS CH, DECODE(MOD(V,7),1,'V1',2,'V2',3,'V3',4,'V4',5,'V5',6,'V6',0,'V7') AS VAL, V
FROM
T A
UNION ALL
SELECT TO_CHAR(V) AS CH, DECODE(MOD(V,7),1,'V1',2,'V2',3,'V3',4,'V4',5,'V5',6,'V6',0,'V7') AS VAL, V
FROM
T B)
)
PIVOT
(
MAX(CH)
FOR VAL IN('V1' V1,'V2' V2,'V3' V3,'V4' V4,'V5' V5,'V6' V6,'V7' V7)
)
ORDER BY GB1, GB2;
저혼자 어찌저찌 풀긴했는데.. 해답을 보고 더 배워야겠네요 그래도 혼자해결해서 매우 뿌듯합니다.
예전방식으로 풀기는 했는데 pivot형식은 다시도전해봐야겠어요 ^^
WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
, t1 as (
select a.*
,decode(mod(a.v,7),0,7,mod(a.v,7)) c_v
,ceil(a.v/7) gn
from t a
)
select a.gn
,b.lvl
,max(decode(a.c_v,1,decode(b.lvl,1,a.c,2,a.v))) v1
,max(decode(a.c_v,2,decode(b.lvl,1,a.c,2,a.v))) v2
,max(decode(a.c_v,3,decode(b.lvl,1,a.c,2,a.v))) v3
,max(decode(a.c_v,4,decode(b.lvl,1,a.c,2,a.v))) v4
,max(decode(a.c_v,5,decode(b.lvl,1,a.c,2,a.v))) v5
,max(decode(a.c_v,6,decode(b.lvl,1,a.c,2,a.v))) v6
,max(decode(a.c_v,7,decode(b.lvl,1,a.c,2,a.v))) v7
from t1 a
,(select level lvl from dual connect by level<=2) b
where 1=1
group by
a.gn
,b.lvl
order by
a.gn
,b.lvl
-- 정답 쿼리 보완 : 불필요 인라인뷰 제거, UNPIVOT/PIVOT 연달아 사용 가능 --
SELECT *
FROM (SELECT c
, TO_CHAR(v) v
, CEIL(v / 7) gb1
, MOD(v - 1, 7) + 1 gb3
FROM t
)
UNPIVOT ( v FOR gb2 IN (c AS 1, v AS 2) )
PIVOT ( MIN(v) FOR gb3 IN (1 v1, 2 v2, 3 v3, 4 v4, 5 v5, 6 v6, 7 v7) )
ORDER BY gb1, gb2
;
) 버튼을 클릭하여 작성 하시면 됩니다.