SELECT 'ASIA' REGION,'INDONESIA' COUNTRY,'REFRIGERATOR' PRODUCT,'LG' BRAND_TOP1,'SAMSUNG' BRAND_TOP2,'SANYO' BRAND_TOP3 FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','LG','SAMSUNG','HITACHI' FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','SAMSUNG','MODENA','MIDEA' FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','SHARP','SANYO','SANKEN' FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','LG','SAMSUNG','MIDEA' FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','MITSHUBUSHI','MIDEA','SANKEN' FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','LG','TOSHIBA','SANKEN' FROM DUAL
UNION ALL SELECT 'ASIA','INDONESIA','REFRIGERATOR','LG','SAMSUNG','MIDEA' FROM DUAL
UNION ALL SELECT 'EUROPE','GERMANY','REFRIGERATOR','LG','SAMSUNG','AMICA' FROM DUAL
UNION ALL SELECT 'EUROPE','GERMANY','REFRIGERATOR','LG','SAMSUNG','SAMSUNG' FROM DUAL
UNION ALL SELECT 'EUROPE','GERMANY','REFRIGERATOR','LG','BLOMBERG','SAMSUNG' FROM DUAL

이전에 해주신 SQL 정말 감사했습니다.

그때 거기선 ASIA 하나만 있었는데..위와 같이 ASIA , EUROPE 즉, REGION 에는 하나 이상이 생길수 가 있거든여..ㅠㅠ 모든 REGION에 대해서 나오게 하고 싶습니다.

아래 SQL 은 이전에 마농님께서 해주셨던 SQL 입니다.

제가 부탁했던거 하나의 AISA 만 부탁해서 그랬는데,,,그 이상 데이타가 있는 경우 각 REGION 에 대해서 나오게 하고 싶습니다.

ㅠㅠ 이리저리 님이 주신걸 가지고 컨트롤 해봤지만..잘 안되서여...ㅠㅠ

다시한번 부탁드릴께요..

WITH TB_IN_SI_INFO AS
(
SELECT 'ASIA' REGION, 'INDONESIA' COUNTRY, 'REFRIGERATOR' PRODUCT, 'SAMSUNG' BRAND_TOP1, 'MIDEA' BRAND_TOP2, 'SANYO' BRAND_TOP3 FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'SHARP', 'MODENA', 'MIDEA' FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'MITSHUBUSHI', 'SANYO', 'MIDEA' FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'LG', 'SAMSUNG', 'MIDEA' FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'LG', 'SAMSUNG', 'HITACHI' FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'LG', 'SAMSUNG', 'SANKEN' FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'LG', 'SAMSUNG', 'SANKEN' FROM DUAL
UNION ALL SELECT 'ASIA', 'INDONESIA', 'REFRIGERATOR', 'LG', 'TOSHIBA', 'SANKEN' FROM DUAL
)
SELECT REGION, COUNTRY, PRODUCT
     , RN
     , MAX(BRAND_TOP1) BRAND_TOP1
     , MAX(TOP1_CNT  ) TOP1_CNT
     , MAX(BRAND_TOP2) BRAND_TOP2
     , MAX(TOP2_CNT  ) TOP2_CNT
     , MAX(BRAND_TOP3) BRAND_TOP3
     , MAX(TOP3_CNT  ) TOP3_CNT
  FROM (
        SELECT REGION, COUNTRY, PRODUCT
             , BRAND_TOP1
             , BRAND_TOP2
             , BRAND_TOP3
             , COUNT(*) CNT
             , NVL2(BRAND_TOP1, COUNT(*), NULL) TOP1_CNT
             , NVL2(BRAND_TOP2, COUNT(*), NULL) TOP2_CNT
             , NVL2(BRAND_TOP3, COUNT(*), NULL) TOP3_CNT
             , RANK() OVER(
               PARTITION BY GROUPING_ID(BRAND_TOP1, BRAND_TOP2, BRAND_TOP3)
               ORDER BY COUNT(*) DESC) RK
             , ROW_NUMBER() OVER(
               PARTITION BY GROUPING_ID(BRAND_TOP1, BRAND_TOP2, BRAND_TOP3)
               ORDER BY COUNT(*) DESC) RN
          FROM TB_IN_SI_INFO
         GROUP BY REGION, COUNTRY, PRODUCT
             , GROUPING SETS ((BRAND_TOP1), (BRAND_TOP2), (BRAND_TOP3))
        )
 WHERE 1=1
 AND RK = 1
 GROUP BY REGION, COUNTRY, PRODUCT, RN
 ORDER BY REGION, COUNTRY, PRODUCT, RN
;