[오라클] 테이블 전치

메모리상의 템프 테이블 생성(실제 테이블생성아님)하여 테스트함. 사용하고자 하는 select구문 위해서 같이 실행

 

1. Hierarchical Queries(계층구조)를 사용

WITH BACK AS
     (  SELECT 'mbc' name, '2005' year1, 'A' gubun
          FROM DUAL
        UNION ALL
        SELECT 'mbc' , '2006' , 'B'
          FROM DUAL
        UNION ALL
        SELECT 'mbc' , '2007' , 'C'
          FROM DUAL
        UNION ALL
        SELECT 'sbs' , '2007' , 'C'
          FROM DUAL
          )



--1. Hierarchical Queries(계층구조)를 사용
SELECT  name
               ,SUBSTR(MAX(SYS_CONNECT_BY_PATH(gubun,'|')),2) gubun
FROM (select  name
                       ,ROW_NUMBER () OVER (PARTITION BY name ORDER BY name, year1) seq
                       ,year1
                       ,gubun
           from   BACK ) 
START WITH seq = 1
CONNECT BY PRIOR name = name
AND PRIOR seq = seq - 1
GROUP BY  name

 

---

2. 분석 함수(RANK)을 사용

SELECT  t1.name
       ,max(decode(no, 1, gubun)) minor1
       ,max(decode(no, 2, gubun)) minor2
       ,max(decode(no, 3, gubun)) minor3
FROM ( SELECT name
              ,gubun
              ,RANK() OVER (PARTITION BY name ORDER BY gubun ) as no
       FROM BACK
       GROUP BY name,gubun
     ) t1
GROUP BY t1.name

 

---

3. DECODE 함수를 사용

select name
       ,max(decode(year1,'2005',gubun)) varA
       ,max(decode(year1,'2006',gubun)) varB
       ,max(decode(year1,'2007',gubun)) varC
from   BACK
group by name;

 

* 통계분석연구회 : http://cafe.daum.net/statsas