데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

ms-sql summary 출력 도움 부탁드립니다.

작성자

개발자

작성일

2016-12-22 23:17ⓒ
2016-12-22 23:19ⓜ

조회수

3,374

WITH t AS
(
SELECT '내부' inout, '부품1' gubun1, '지점1' gubun2, 11.0 cost, 0.1 per
UNION ALL SELECT '내부', '부품1', '지점2', 22.0, 0.2
UNION ALL SELECT '외부', '부품1', '지점2', 3.0, 0.3
UNION ALL SELECT '내부', '부품2', '지점1', 4.0, 0.4
UNION ALL SELECT '내부', '부품2', '지점2', 5.0, 0.5
UNION ALL SELECT '외부', '부품2', '지점2', 6.0, 0.6
UNION ALL SELECT '내부', '부품3', '지점1', 7.0, 0.7
UNION ALL SELECT '외부', '부품3', '지점1', 8.0, 0.8
UNION ALL SELECT '외부', '부품3', '지점1', 9.0, 0.9
)
SELECT CASE GROUPING_ID(inout, gubun2)
       WHEN 3 THEN 'black'
       WHEN 2 THEN 'white'
       WHEN 1 THEN CASE inout WHEN '내부' THEN 'green' ELSE 'red' END
       WHEN 0 THEN 'white'
        END color
     , gubun1
     , ISNULL(inout, '내부+외부') inout
     , ISNULL(gubun2, 'Total') gubun2
     , gubun3
     , SUM(v) v
FROM (SELECT CAST(inout AS VARCHAR(20)) inout
             , gubun1
             , CAST(gubun2 AS VARCHAR(20)) gubun2
             , CAST(cost   AS NUMERIC(9,1)) Cost
             , CAST(per    AS NUMERIC(9,1)) Per
          FROM t
        ) a
UNPIVOT (v FOR gubun3 IN (cost, per)) a
GROUP BY gubun1, gubun3, CUBE(inout, gubun2)
ORDER BY a.gubun1, a.inout, a.gubun2, a.gubun3
;