컬럼 <=> 행 변환

WITH TMP AS
(
SELECT WORD
       ,SUBSTR(WORD, ROWNUM, 1) COL_TO_ROWS
FROM(
    SELECT 'ADSFGWEQWE' WORD FROM DUAL
    )
CONNECT BY LEVEL <= LENGTH(WORD)
)
, TMP2 AS
(
SELECT DECODE(ROWNUM,1,COL_TO_ROWS) "1"
      ,DECODE(ROWNUM,2,COL_TO_ROWS) "2"
      ,DECODE(ROWNUM,3,COL_TO_ROWS) "3"
      ,DECODE(ROWNUM,4,COL_TO_ROWS) "4"
      ,DECODE(ROWNUM,5,COL_TO_ROWS) "5"
      ,DECODE(ROWNUM,6,COL_TO_ROWS) "6"
      ,DECODE(ROWNUM,7,COL_TO_ROWS) "7"
      ,DECODE(ROWNUM,8,COL_TO_ROWS) "8"
      ,DECODE(ROWNUM,9,COL_TO_ROWS) "9"
      ,DECODE(ROWNUM,10,COL_TO_ROWS) "10"
  FROM TMP
)
--SELECT * FROM TMP2;
SELECT MAX("1")||MAX("2")||MAX("3")||MAX("4")||MAX("5")||
       MAX("6")||MAX("7")||MAX("8")||MAX("9")||MAX("10") ROWS_TO_COL
FROM TMP2;

 

TMP2 결과

TMP2 합친 결과

 

컬럼을 행으로 변환할 때는 행을 반복적으로 실행시켜 나눌 수 있지만 본문 "COL_TO_ROWS"

반대로 합칠 때는 수동으로 입력하는 수 밖에 없다. 본문 "ROWS_TO_COL"

WITH R10 AS --미리 만들어둔 반복용 테이블이라 가정
(
SELECT 1 AS NUM FROM DUAL UNION ALL
SELECT 2 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 4 AS NUM FROM DUAL UNION ALL
SELECT 5 AS NUM FROM DUAL UNION ALL
SELECT 6 AS NUM FROM DUAL UNION ALL
SELECT 7 AS NUM FROM DUAL UNION ALL
SELECT 8 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL UNION ALL
SELECT 10 AS NUM FROM DUAL
)
, TMP AS
(
SELECT WORD
       ,SUBSTR(WORD, ROWNUM, 1) COL_TO_ROWS
FROM(
    SELECT 'ADSFGWEQWE' WORD FROM DUAL
    )
    , R10 --데카르트 곱으로 활용
 WHERE R10.NUM <= LENGTH(WORD)  
--CONNECT BY LEVEL <= LENGTH(WORD)

)
, TMP2 AS
(
SELECT DECODE(ROWNUM,1,COL_TO_ROWS) "1"
      ,DECODE(ROWNUM,2,COL_TO_ROWS) "2"
      ,DECODE(ROWNUM,3,COL_TO_ROWS) "3"
      ,DECODE(ROWNUM,4,COL_TO_ROWS) "4"
      ,DECODE(ROWNUM,5,COL_TO_ROWS) "5"
      ,DECODE(ROWNUM,6,COL_TO_ROWS) "6"
      ,DECODE(ROWNUM,7,COL_TO_ROWS) "7"
      ,DECODE(ROWNUM,8,COL_TO_ROWS) "8"
      ,DECODE(ROWNUM,9,COL_TO_ROWS) "9"
      ,DECODE(ROWNUM,10,COL_TO_ROWS) "10"
  FROM TMP
)
--SELECT * FROM TMP2;
SELECT MAX("1")||MAX("2")||MAX("3")||MAX("4")||MAX("5")||
       MAX("6")||MAX("7")||MAX("8")||MAX("9")||MAX("10") ROWS_TO_COL
FROM TMP2;

CONNECT BY 를 꼭 안쓰더라도 위 처럼 사용하는 경우도 많다.