다음과 같이 자동차 보험상품에 관한 테이블이 있습니다.

WITH BOHUM_HEAD AS (
SELECT ’A’ CODE,’대인’ NAME FROM DUAL UNION ALL
SELECT ’B’ CODE,’대물’ NAME FROM DUAL UNION ALL
SELECT ’C’ CODE,’자손’ NAME FROM DUAL UNION ALL
SELECT ’D’ CODE,’자차’ NAME FROM DUAL UNION ALL
SELECT ’E’ CODE,’무보험’ NAME FROM DUAL
)

고객이 자동차 보험을 가입할 때 대인은 반드시 가입해야 하며 나머지는 선택입니다. 그렇다면 고객이 가입할 수 있는 모든 경우의 수는 아래와 같습니다.

위와 같은 결과를 보기 위해 나름 쿼리를 만들어 보았습니다.

SELECT
        SUBSTR(COL1,1,1)
               ||DECODE(COL2,NULL,NULL,’+’||SUBSTR(COL2,1,1))
               ||DECODE(COL3,NULL,NULL,’+’||SUBSTR(COL3,1,1))
               ||DECODE(COL4,NULL,NULL,’+’||SUBSTR(COL4,1,1))
               ||DECODE(COL5,NULL,NULL,’+’||SUBSTR(COL5,1,1)) CODE
       ,SUBSTR(COL1,2)
              ||DECODE(COL2,NULL,NULL,’+’||SUBSTR(COL2,2))
              ||DECODE(COL3,NULL,NULL,’+’||SUBSTR(COL3,2))
              ||DECODE(COL4,NULL,NULL,’+’||SUBSTR(COL4,2))
              ||DECODE(COL5,NULL,NULL,’+’||SUBSTR(COL5,2)) NAME
FROM
(
          SELECT
                MIN(DECODE(CODE,’A’,CODE))||MIN(DECODE(CODE,’A’,NAME)) COL1
                ,MIN(DECODE(CODE,’B’,CODE))||MIN(DECODE(CODE,’B’,NAME)) COL2
                ,MIN(DECODE(CODE,’C’,CODE))||MIN(DECODE(CODE,’C’,NAME)) COL3
               ,MIN(DECODE(CODE,’D’,CODE))||MIN(DECODE(CODE,’D’,NAME)) COL4
               ,MIN(DECODE(CODE,’E’,CODE))||MIN(DECODE(CODE,’E’,NAME)) COL5
 FROM BOHUM_HEAD
)
GROUP BY COL1,GROUPING SETS( 
             (NULL),(COL2),(COL3),(COL4),(COL5)
             ,(COL2,COL3),(COL2,COL4),(COL2,COL5),(COL3,COL4),(COL3,COL5),(COL4,COL5)
            ,(COL2,COL3,COL4),(COL2,COL3,COL5),(COL2,COL4,COL5)
            ,(COL2,COL3,COL4,COL5)
)
ORDER BY GROUPING(COL1)+GROUPING(COL2)+GROUPING(COL3)+GROUPING(COL4)+GROUPING(COL5) DESC
,GROUPING_ID(COL1,COL2,COL3,COL4,COL5)

그런데 암만 생각해 보아도 GROUPING SETS 부분이 너무 무지막한 것 같네요. 좀 더 효율적인 쿼리를 만들 수는 없을까요? 나아가 만일 상품종류가 추가되기라도 한다면 쿼리가 많이 변경되야 할 것입니다. 나중에 상품이 추가되더라도 쿼리가 변경되지 않거나 변경되더라도 최소화 할 수 있는 방법이 없을까요?