|
SQL :
WITH DATA1 AS (
SELECT '인건비' AS NAME, '2010.01.01' AS DATE1, 1 AS NUMBER1, 100 AS AMT1, NULL AS AMT2, '비고1' AS NOTES FROM DUAL UNION ALL
SELECT '인건비' AS NAME, '2010.01.01' AS DATE1, 1 AS NUMBER1, 100 AS AMT1, NULL AS AMT2, '비고2' AS NOTES FROM DUAL UNION ALL
SELECT '인건비' AS NAME, '2010.02.01' AS DATE1, 2 AS NUMBER1, 110 AS AMT1, NULL AS AMT2, '비고3' AS NOTES FROM DUAL UNION ALL
SELECT '인건비' AS NAME, '2010.03.03' AS DATE1, 1 AS NUMBER1, 110 AS AMT1, NULL AS AMT2, '비고4' AS NOTES FROM DUAL UNION ALL
SELECT '인건비' AS NAME, '2010.03.04' AS DATE1, 1 AS NUMBER1, NULL AS AMT1, 50 AS AMT2, '비고5' AS NOTES FROM DUAL UNION ALL
SELECT '차량유지비' AS NAME, '2010.02.14' AS DATE1, 300 AS NUMBER1, 50 AS AMT1, NULL AS AMT2, '비고6' AS NOTES FROM DUAL UNION ALL
SELECT '차량유지비' AS NAME, '2010.02.26' AS DATE1, 3 AS NUMBER1, NULL AS AMT1, 20 AS AMT2, '비고7' AS NOTES FROM DUAL UNION ALL
SELECT '차량유지비' AS NAME, '2010.03.15' AS DATE1, 50 AS NUMBER1, 100 AS AMT1, NULL AS AMT2, '비고8' AS NOTES FROM DUAL
)
SELECT DECODE(A.GUBUN, 0, A.NAME, 1, A.NAME || ' 계', 3, '총계') AS "계정명"
, A.DATE1 AS "전표일자"
, A.NOTES
, DECODE(A.GUBUN, 0, A.NUMBER1, 1, NULL) AS "일련번호"
, DECODE(A.GUBUN, 0, A.AMT1, 1, A.AMT1 - A.AMT2, 3, AMT1) AS "차변금액"
, DECODE(A.GUBUN, 0, A.AMT2, 1, NULL, 3, AMT2) AS "대변금액"
, GUBUN
FROM (
SELECT A.NAME
, A.DATE1
, A.NOTES
, MIN(A.NUMBER1) AS NUMBER1
, SUM(AMT1) AS AMT1
, SUM(AMT2) AS AMT2
, GROUPING_ID(A.NAME, A.DATE1) AS GUBUN
FROM DATA1 A
GROUP BY ROLLUP(a.name, (a.date1, a.number1, a.notes))
) A ;
|
