WITH T1 AS (
SELECT 'A' K1, 1 AS K2  FROM dual UNION ALL
SELECT 'A', 2 FROM dual UNION ALL
SELECT 'A', 3 FROM dual UNION ALL
SELECT 'B', 1 FROM dual UNION ALL
SELECT 'B', 2 FROM dual UNION ALL
SELECT 'C', 1 FROM duaL
)
SELECT T1.K1, T2.LV FROM T1 , (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 3) T2
GROUP BY T1.K1 , T2.LV
ORDER BY T1.K1

 감사합니다. 

박성빈님..뒤에 key2가 숫자가 아닌 문자열로 바꿔야 할거 같아 본문 내용 수정합니다.

문자열로 변경하였습니다~_~;;;;

WITH T1 AS (
SELECT 'A' K1, 'TEST01' AS K2  FROM dual UNION ALL
SELECT 'A', 'TEST02' FROM dual UNION ALL
SELECT 'A', 'TEST03' FROM dual UNION ALL
SELECT 'B', 'TEST02' FROM dual UNION ALL
SELECT 'B', 'TEST03' FROM dual UNION ALL
SELECT 'C', 'TEST03' FROM duaL
 )
SELECT T1.K1, T2.K2 FROM T1 , (SELECT K2 FROM T1 GROUP BY K2) T2
GROUP BY T1.K1, T2.K2 ;

 박성빈님 정말 죄송합니다. key3 컬럼을 하나더 추가 하고 싶습니다.

숫자 컬럼으로써 값이 없을때는 0로 표현해주고 싶습니다.

퇴근해야해서 급하게 만들어봤어요;;

아마 맞을꺼에요;;

근데 데이터량을 생각 안하고 만든거라;;; 속도는 ;;;

WITH T1 AS (
SELECT 'A' K1, 'TEST01' K2 , 10 AS K3  FROM DUAL UNION ALL
SELECT 'A', 'TEST03', 40 FROM DUAL UNION ALL
SELECT 'B', 'TEST02', 15 FROM DUAL UNION ALL
SELECT 'B', 'TEST03', 25 FROM DUAL UNION ALL
SELECT 'C', 'TEST03', 5 FROM DUAL
  )
 SELECT A.K1
            , B.K2
            , NVL((SELECT K3 FROM T1 C WHERE C.K1 = A.K1 AND C.K2 = B.K2 AND ROWNUM = 1), 0) AS CNT
 FROM T1 A, (SELECT K2 FROM T1 GROUP BY K2) B
 GROUP BY A.K1, B.K2 ;

-- 1. Partition Outer Join --
SELECT b.key1
     , a.key2
     , NVL(b.key3, 0) key3
  FROM (SELECT 'TEST01' key2 FROM dual
        UNION ALL SELECT 'TEST02' FROM dual
        UNION ALL SELECT 'TEST03' FROM dual
        ) a
  LEFT OUTER JOIN t1 b
 PARTITION BY (b.key1)
    ON a.key2 = b.key2
 ORDER BY key1, key2
;

-- 2. Model --
SELECT *
  FROM t1
 MODEL
 PARTITION BY (key1)
 DIMENSION BY (key2)
 MEASURES (key3)
 IGNORE NAV
 RULES(key3[FOR key2 IN ('TEST01', 'TEST02', 'TEST03')] = key3[CV()])
 ORDER BY key1, key2
;

 한가지만 더 여쭙겠습니다.

아래처럼 KEY1 또는 KEY2가 null이 들어올때는 KEY3값이 모두 나오지 않는거 같습니다.

모두 나오게 하려면 어떻게 해야할지 문의 드립니다.

WITH T2(KEY1, KEY2, KEY3 ) AS(

SELECT 'A' KEY1, 'TEST01', 10 AS KEY3  FROM DUAL UNION ALL 

SELECT 'A', 'TEST02', 0 FROM DUAL UNION ALL

SELECT 'A', 'TEST03', 40 FROM DUAL UNION ALL

SELECT 'B', 'TEST01', 0 FROM DUAL UNION ALL

SELECT 'B', NULL, 15 FROM DUAL UNION ALL

SELECT 'B', 'TEST03', 25 FROM DUAL UNION ALL

SELECT 'C', 'TEST01', 0 FROM DUAL UNION ALL

SELECT 'C', 'TEST02', 0 FROM DUAL UNION ALL

SELECT 'C', 'TEST03', 5 FROM DUAL

)

 SELECT A.KEY1

            , B.KEY2

            , NVL((SELECT KEY3 FROM T2 C WHERE C.KEY1 = A.KEY1 AND C.KEY2 = B.KEY2 AND ROWNUM = 1), 0) AS CNT

 FROM T2 A, (SELECT KEY2 FROM T2 GROUP BY KEY2) B

 GROUP BY A.KEY1, B.KEY2 

ORDER BY KEY1,KEY2;

한가지만 더 여쭙겠습니다.

아래처럼 KEY1 또는 KEY2가 null이 들어올때는 KEY3값이 모두 나오지 않는거 같습니다.

모두 나오게 하려면 어떻게 해야할지 문의 드립니다.

이게 무슨 얘기이신지 잘 모르겠네요 원하시는 형태를 얘기해주세요;;;

이렇게 나오면 되나요?

WITH T2 AS(
SELECT 'A' KEY1, 'TEST01' as KEY2, 10 AS KEY3  FROM DUAL UNION ALL
SELECT 'A', 'TEST02', 0 FROM DUAL UNION ALL
SELECT 'A', 'TEST03', 40 FROM DUAL UNION ALL
SELECT 'B', 'TEST01', 0 FROM DUAL UNION ALL
SELECT 'B', NULL, 15 FROM DUAL UNION ALL
SELECT 'B', 'TEST03', 25 FROM DUAL UNION ALL
SELECT 'C', 'TEST01', 0 FROM DUAL UNION ALL
SELECT 'C', 'TEST02', 0 FROM DUAL UNION ALL
SELECT 'C', 'TEST03', 5 FROM DUAL
)
SELECT A.KEY1
           , B.KEY2
           , CASE WHEN B.KEY2 IS NULL
           THEN NVL((SELECT KEY3 FROM T2 C WHERE C.KEY1 = A.KEY1 AND C.KEY2 IS NULL AND ROWNUM = 1), 0)
           ELSE NVL((SELECT KEY3 FROM T2 C WHERE C.KEY1 = A.KEY1 AND C.KEY2 = B.KEY2 AND ROWNUM = 1), 0) END  AS CNT
FROM T2 A, (SELECT KEY2 FROM T2 GROUP BY KEY2) B
GROUP BY A.KEY1, B.KEY2
ORDER BY KEY1,KEY2;

 와우..^^; 

감사합니다. 박성빈님

또 마농님^^;