| |
SELECT (CASE WHEN GRP_ID = '0' THEN PLAN_YM
WHEN MM_COUNT < 6 THEN '소계'
WHEN MM_COUNT = 6 AND FIRST_SECOND_TYPE = '0' THEN '상반기'
WHEN MM_COUNT = 6 AND FIRST_SECOND_TYPE = '1' THEN '하반기'
WHEN MM_COUNT = 12 THEN '년계'
ELSE '총계' END) AS PLAN_YM
--실 컬럼명은 변수로 받아야 됨
, DECODE(COL_GUBN_GRP, 1, '비중', 2, '매출액', 3, '이익금') AS COL_NAME
, FIRST_SECOND_TYPE --0:상반기, 1:하반기
, MM_COUNT --1:Table 컬럼 sum()값. 2이상:소계, 합계 컬럼임
, GRP_ID --GROUPING_ID()함수 결과 값.
, COL_ROWNUM --컬럼 ROWNUM
, COL_GUBN_GRP --반복컬럼이 1개 이상일 경우 순서
FROM (
WITH YY_MM AS
(
SELECT YEAR_DATA
, PLAN_YM
, FIRST_SECOND_TYPE
, COUNT(FIRST_SECOND_TYPE) AS MM_COUNT
, GROUPING_ID(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM) AS GRP_ID
FROM (SELECT SUBSTR(A.PLAN_YM, 1,4) AS YEAR_DATA
, A.PLAN_YM AS PLAN_YM
, FLOOR ((SUBSTR(A.PLAN_YM,5,2) - 1) / 6) AS FIRST_SECOND_TYPE
FROM ( --2013.10 ~ 201502 월까지 존재 하는 테이블
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201310', 'YYYYMM'), ROWNUM), 'YYYYMM') AS PLAN_YM
FROM DUAL
CONNECT BY LEVEL <= 17
) A
)
GROUP BY ROLLUP(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM)
)
SELECT PLAN_YM
, FIRST_SECOND_TYPE
, MM_COUNT
, GRP_ID
--** 맨 앞쪽 1은 변수로 받아야 됨. (반복컬럼 갯수 임)
, DECODE(3, 1, ROWNUM, 2, (ROWNUM * 2) - 1, (ROWNUM * 3) - 2) AS COL_ROWNUM
, 1 AS COL_GUBN_GRP
FROM YY_MM
WHERE GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3)
UNION ALL
SELECT PLAN_YM
, FIRST_SECOND_TYPE
, MM_COUNT
, GRP_ID
--** 맨 앞쪽 2는 변수로 받아야 됨. (반복컬럼 갯수 임)
, DECODE(3, 2, (ROWNUM * 2) - 0, (ROWNUM * 3) - 1) AS COL_ROWNUM
, 2 AS COL_GUBN_GRP
FROM YY_MM
WHERE ( GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3) )
AND 2 <= 2 --** 오른쪽 2는 변수로 받아야 됨. (반복컬럼 갯수 임)
UNION ALL
SELECT PLAN_YM
, FIRST_SECOND_TYPE
, MM_COUNT
, GRP_ID
, (ROWNUM * 3) - 0 AS COL_ROWNUM
, 3 AS COL_GUBN_GRP
FROM YY_MM
WHERE ( GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3) )
AND 3 = 3 --** 오른쪽 3은 변수로 받아야 됨. (반복컬럼 갯수 임)
)
ORDER BY COL_ROWNUM ;
|
