원하시는 데이터가 이게 맞나 모르겠네요.
WITH T AS (SELECT '201507' YM, '단가' COL1, 1000 COL2 FROM DUAL UNION ALL SELECT '201507' YM, '금액' COL1, 10000 COL2 FROM DUAL UNION ALL SELECT '201508' YM, '단가' COL1, 2000 COL2 FROM DUAL UNION ALL SELECT '201508' YM, '금액' COL1, 20000 COL2 FROM DUAL) SELECT YM, COL1, COL2 FROM (SELECT YM, COL1, COL2 FROM T UNION ALL SELECT YM, '금액/단가' COL1, P2 / P1 COL2 FROM (SELECT YM, MAX(DECODE(COL1, '단가', COL2)) P1 , MAX(DECODE(COL1, '금액', COL2)) P2 FROM T GROUP BY YM)) ORDER BY YM, INSTR('단가,금액,금액/단가', COL1) ASC;
SELECT YM , DECODE(COL1, NULL, '금액/단가', COL1) , DECODE(GROUPING(COL1), 1 , LAG(SUM(COL2), 1, 0) OVER (ORDER BY YM, GROUPING(COL1), COL1 DESC) / (SUM(COL2) - LAG(SUM(COL2), 1, 0) OVER (ORDER BY YM, GROUPING(COL1), COL1 DESC)) , SUM(COL2)) COL2 FROM T GROUP BY YM, ROLLUP(COL1) ORDER BY YM, GROUPING(COL1), COL1 DESC ;
WITH t AS ( SELECT '201507' ym, '단가' col1, 1000 col2 FROM dual UNION ALL SELECT '201507', '금액', 10000 FROM dual UNION ALL SELECT '201508', '단가', 2000 FROM dual UNION ALL SELECT '201508', '금액', 20000 FROM dual ) SELECT * FROM t MODEL PARTITION BY (ym) DIMENSION BY (CAST(col1 AS VARCHAR2(20)) col1) MEASURES (col2) RULES (col2['금액/단가'] = col2['금액'] / col2['단가']) ORDER BY ym, DECODE(col1, '단가', 1, '금액', 2, 3) ;