꿈꾸는 개발자, DBA 커뮤니티 구루비

[퀴즈] 행을 열로, 열을 행으로 변환

by 마농 PIVOT UNPIVOT [2012.02.14 17:47:19]

[퀴즈] 행을 열로, 열을 행으로 변환

[원본]과 같이 26개의 알파벳과 순번이 주어졌을 때
[결과]와 같이 구분컬럼과 7개의 컬럼으로 조회되는 쿼리를 작성하세요.

WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
SELECT *
FROM t
;

[원본]		[결과]
C	V	GB1	GB2	V1	V2	V3	V4	V5	V6	V7
A	1	1	1	A	B	C	D	E	F	G
B	2	1	2	1	2	3	4	5	6	7
C	3	2	1	H	I	J	K	L	M	N
D	4	2	2	8	9	10	11	12	13	14
E	5	3	1	O	P	Q	R	S	T	U
F	6	3	2	15	16	17	18	19	20	21
G	7	4	1	V	W	X	Y	Z
H	8	4	2	22	23	24	25	26
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

[정답보기] 트리플클릭.
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
;

by 제로 [2012.02.16 09:33:28]

뭔가 부족한 느낌이 드네요...;;

WITH t AS
(
SELECT CHR(LEVEL + 64) c
, LEVEL v
FROM dual
CONNECT BY LEVEL <= 26
)
select *
from (select decode(gb2, 2, v, c) c
, v1
, gb1
, gb2
from (SELECT min(c) c
, to_char(min(v)) v
, min(decode(mod(v,7), 0, 7, mod(v,7))) v1
, min(ceil(v/7)) gb1
, row_number() over(partition by min(v) order by c) gb2
FROM t
group by c, rollup(1) ) )
pivot (min(c)
for v1 in (1 v1, 2 v2, 3 v3, 4 v4, 5 v5, 6 v6, 7 v7) )
order by gb1, gb2;

by v상이v [2012.02.16 13:19:25]

10g라서 아직 pivot 을 해본적이 없네요...;
MIN(DECODE())로 해봤습니다.
PIVOT 함수 사용했으면 바깥 select만 PIVOT로 구현했을듯 합니다...

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 MAX(CEIL(V/7)) GB1
,GROUPING(CEIL(V/7)) + 1 GB2
,MIN(V) - (CEIL(MIN(V)/7) - 1) * 7 GB3
,DECODE(GROUPING(CEIL(V/7)),0,C,MIN(V)) V
FROM T
GROUP BY C, ROLLUP(CEIL(V/7))
)
GROUP BY GB1,GB2
ORDER BY GB1,GB2

by 샤프하나 [2012.02.16 15:21:00]

힌트는 생각도 안하고 한번 구현해봤어여.. --a

SELECT GB1 ,GB2
,MIN(V1) AS V1 ,MIN(V2) AS V2 ,MIN(V3) AS V3
,MIN(V4) AS V4 ,MIN(V5) AS V5 ,MIN(V6) AS V6
,MIN(V7) AS V7
FROM (
SELECT DECODE(MOD(t.V,7),1,DECODE(DU.LV,1,C,V)) AS V1
,DECODE(MOD(t.V,7),2,DECODE(DU.LV,1,C,V)) AS V2
,DECODE(MOD(t.V,7),3,DECODE(DU.LV,1,C,V)) AS V3
,DECODE(MOD(t.V,7),4,DECODE(DU.LV,1,C,V)) AS V4
,DECODE(MOD(t.V,7),5,DECODE(DU.LV,1,C,V)) AS V5
,DECODE(MOD(t.V,7),6,DECODE(DU.LV,1,C,V)) AS V6
,DECODE(MOD(t.V,7),0,DECODE(DU.LV,1,C,V)) AS V7
,CEIL(t.V/7) AS GB1
,DU.LV AS GB2
FROM t
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2) DU
)
GROUP BY GB1, GB2
ORDER BY GB1, GB2

by 마농 [2012.02.17 10:01:34]

정답 올렸습니다.
11G에서 새롭게 등장한 PIVOT 과 UNPIVOT 을 이용해 풀어봤습니다.
열을 행으로 바꾸기 위해 사용한 Rollup 이나 Connect by level 방식 대신 Unpivot 을 사용했구요.
행을 열로 바꾸기 위해 사용한 Group By Min(Decode 방식대신 Pivot 을 사용했습니다.

by 쏘... [2012.02.17 15:37:48]

마농님 쿼리 잘 봤습니다.
한가지..

unpivot 과 pivot을 연속적으로 사용할 경우, 매번 인라인뷰로 묶어줄 필요는 없습니다.
그냥 단순히 아래와 같이 하면 되죠.

SELECT *
FROM (SELECT C
, 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

by 마농 [2012.02.17 15:59:10]

와우~ 그렇군요 ^^

by 산타 [2012.02.28 17:07:10]