WITH locations AS
(
SELECT '서울' loc, '한국' p_loc FROM dual
UNION ALL SELECT '부산'     , '한국'   FROM dual
UNION ALL SELECT '대구'     , '한국'   FROM dual
UNION ALL SELECT '구로구'   , '서울'   FROM dual
UNION ALL SELECT '구로동'   , '구로구' FROM dual
UNION ALL SELECT '영등포구' , '서울'   FROM dual
UNION ALL SELECT '사하구'   , '부산'   FROM dual
UNION ALL SELECT '서구'     , '대전'   FROM dual
UNION ALL SELECT '관악구'   , '서울'   FROM dual
UNION ALL SELECT '신림동'   , '관악구' FROM dual
UNION ALL SELECT '봉천동'   , '관악구' FROM dual
UNION ALL SELECT '과정제1동', '사하구' FROM dual
UNION ALL SELECT '과정제2동', '사하구' FROM dual
UNION ALL SELECT '둔산동'   , '서구'   FROM dual
)
, products AS
(
SELECT '대형가전' cd1, '냉장고' cd2, '삼성냉장고1' cd3 FROM dual
UNION ALL SELECT '대형가전', '냉장고'    , '삼성냉장고2' FROM dual
UNION ALL SELECT '대형가전', '냉장고'    , '엘지냉장고1' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '대우세탁기1' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '대우세탁기2' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '삼성세탁기1' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '삼성세탁기2' FROM dual
UNION ALL SELECT '대형가전', '에어컨'    , '엘지에어컨1' FROM dual
UNION ALL SELECT '소형가전', '밥솥'      , '쿠쿠1'       FROM dual
UNION ALL SELECT '소형가전', '밥솥'      , '쿠첸2'       FROM dual
UNION ALL SELECT '소형가전', '전자레인지', '삼성레인지'  FROM dual
UNION ALL SELECT '기타가전', '면도기'    , '필립스1'     FROM dual
UNION ALL SELECT '기타가전', '면도기'    , '필립스2'     FROM dual
UNION ALL SELECT '기타가전', '면도기'    , '카이저'      FROM dual
)
, sales AS
(
SELECT '신림동' loc, '삼성냉장고1' cd3 FROM dual
UNION ALL SELECT '신림동'   , '삼성냉장고1' FROM dual
UNION ALL SELECT '봉천동'   , '삼성냉장고2' FROM dual
UNION ALL SELECT '과정제1동', '대우세탁기2' FROM dual
UNION ALL SELECT '과정제1동', '삼성냉장고1' FROM dual
UNION ALL SELECT '과정제1동', '필립스2'     FROM dual
UNION ALL SELECT '봉천동'   , '삼성레인지'  FROM dual
UNION ALL SELECT '과정제2동', '삼성냉장고1' FROM dual
UNION ALL SELECT '신림동'   , '쿠첸2'       FROM dual
UNION ALL SELECT '봉천동'   , '엘지냉장고1' FROM dual
UNION ALL SELECT '과정제1동', '필립스2'     FROM dual
)
, temp1 AS
(
-- 조회 대상 지역 --
SELECT CONNECT_BY_ROOT(loc) r_loc
     , loc
  FROM locations
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH NVL2(:v_loc3, loc, p_loc) = COALESCE(:v_loc3, :v_loc2, :v_loc1, '한국')
 CONNECT BY PRIOR loc = p_loc
)
, temp2 AS
(
-- 조회 대상 제품 --
SELECT DECODE('', :v_cd1, cd1, :v_cd2, cd2) r_cd
     , cd3
  FROM products
 WHERE cd1 = NVL(:v_cd1, cd1)
   AND cd2 = NVL(:v_cd2, cd2)
   AND cd3 = NVL(:v_cd3, cd3)
)
-- 1. Title 부분 --
SELECT '구분' gb
     , a.*
  FROM (SELECT NVL(cd3, '합계') gb
             , ROW_NUMBER() OVER(ORDER BY r_cd, cd3) rn
          FROM temp2
         GROUP BY ROLLUP((r_cd, cd3))
        )
 PIVOT (MIN(gb) FOR rn IN (  1,  2,  3,  4,  5,  6,  7,  8,  9, 10
                          , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
                          ) ) a
 UNION ALL
-- 2. 통계 부분
SELECT r_loc
     , SUM(DECODE(rn,  1, cnt))||'' c01
     , SUM(DECODE(rn,  2, cnt))||'' c02
     , SUM(DECODE(rn,  3, cnt))||'' c03
     , SUM(DECODE(rn,  4, cnt))||'' c04
     , SUM(DECODE(rn,  5, cnt))||'' c05
     , SUM(DECODE(rn,  6, cnt))||'' c06
     , SUM(DECODE(rn,  7, cnt))||'' c07
     , SUM(DECODE(rn,  8, cnt))||'' c08
     , SUM(DECODE(rn,  9, cnt))||'' c09
     , SUM(DECODE(rn, 10, cnt))||'' c10
     , SUM(DECODE(rn, 11, cnt))||'' c11
     , SUM(DECODE(rn, 12, cnt))||'' c12
     , SUM(DECODE(rn, 13, cnt))||'' c13
     , SUM(DECODE(rn, 14, cnt))||'' c14
     , SUM(DECODE(rn, 15, cnt))||'' c15
     , SUM(DECODE(rn, 16, cnt))||'' c16
     , SUM(DECODE(rn, 17, cnt))||'' c17
     , SUM(DECODE(rn, 18, cnt))||'' c18
     , SUM(DECODE(rn, 19, cnt))||'' c19
     , SUM(DECODE(rn, 20, cnt))||'' c20
  FROM (SELECT a.r_loc
             , ROW_NUMBER() OVER(PARTITION BY a.r_loc ORDER BY b.r_cd, b.cd3) rn
             , COUNT(c.loc) cnt
          FROM temp1 a
         CROSS JOIN temp2 b 
          LEFT OUTER JOIN sales c
            ON a.loc = c.loc
           AND b.cd3 = c.cd3
         GROUP BY a.r_loc, b.r_cd, ROLLUP(b.cd3)
        )
 GROUP BY ROLLUP(r_loc)
;

정말 정말 감사합니다 마농님.

작성해 주신 코드 분석 해서 해결해 보겠습니다.

 한가지 더 여쭙자면 이 코드론 상품이 20개 넘어가면 쿼리를 수정해야 하고 상품이 20개 보다 적으면 빈칸이 쭉 나오게 되는데 이런걸 피하려면 프로시져를 이용해서 쿼리를 동적으로 만들어야 하는 건가요?