decode를 이용하지 않고 row를 column으로 나열하는 방법

drop table t;
create table t
( a varchar2(1),
  b number);
insert into t values ('A',1);
insert into t values ('A',2);
insert into t values ('A',3);
insert into t values ('B',1);
insert into t values ('B',2);
insert into t values ('B',3);
insert into t values ('B',4);
insert into t values ('B',5);
insert into t values ('B',6);

 

위의 데이터를

A    1>2>3
B    1>2>3>4>5>6

의 형태로 decode를 이용하지 않고 query하는 방법..
즉, row의 수와 관계없이 그룹핑하고자 하는 칼럼에 대하여 row수만큼 칼럼으로 나열하는 방법입니다.
 

주요 핵심은

1.
start with문에서 제공하는 SYS_CONNECT_BY_PATH함수를 이용한 row의 누적된 칼럼화
(decode문을 쓰지 않아도 된다.
 decode문은 칼럼으로 옮겨야 할 row의 최대수를 알아야만 코딩이 가능하지만,
 위의 방법은 그럴 필요가 전혀없습니다.)
중요한 건 start with문의 sys_connect_by_path를 사용하기 위한 연결고리를 만들어 내는 것..
아래의 경우는 cnt와 rn을 이용하여 임의로 만들었다.

2.
analitic function(오라클 9i부터 제공)을 이용한 row의 grouping이다.

------------------------------------------------------------ 

 1 select *
 2 from (
 3   select t.*, max(rn) over (partition by a) max_rn
 4   from (
 5     select level, a, b, rn, cnt, sw1, sw2,
 6         SYS_CONNECT_BY_PATH(b, ' > ') Path   
 7     from (
 8      select t.*, cnt*1000000+rn sw1,
 9          lag(cnt*1000000+rn) over (partition by a order by cnt*1000000+rn) sw2
10      from (
11       select t.*,
12               row_number() over (partition by a order by b) rn,
13              dense_rank() over (order by a) cnt
14        from t) t
15      )
16   start with sw2 is null
17   connect by prior sw1 = sw2
18   ) t
19 )
20 where rn=max_rn;

------------------------------------------------------------ 
==> 실행결과
LEVEL A   B  RN CNT SW1  SW2        PATH                                                   MAX_RN
  1       3   A  13    3       1    1000003  1000002  > 11 > 12 > 13                           3
  2       6   B  16    6       2    2000006  2000005  > 11 > 12 > 13 > 14 > 15 > 16     6

 

위에서 결과는 PATH 이고, MAX_RN은 row수입니다.

 

 

 

 

====================================================================

응답글

감사합니다 덕분에 문제 해결했습니다. 그런데 한가지 잘못된 점이 있습니다. where rn=max_rn;대신 하위쿼리의 level을 가져와 where lvl=max_rn;으로 바꿨습니다. 1 select * 2 from ( 3 select t.*, max(rn) over (partition by a) max_rn 4 from ( 5 select level as lvl, a, b, rn, cnt, sw1, sw2, 6 SYS_CONNECT_BY_PATH(b, ' > ') Path 7 from ( 8 select t.*, cnt*1000000+rn sw1, 9 lag(cnt*1000000+rn) over (partition by a order by cnt*1000000+rn) sw2 10 from ( 11 select t.*, 12 row_number() over (partition by a order by b) rn, 13 dense_rank() over (order by a) cnt 14 from t) t 15 ) 16 start with sw2 is null 17 connect by prior sw1 = sw2 18 ) t 19 ) 20 where lvl=max_rn;