/***************************************************************************************
-- Title : [2k8] GROUPING SET을 이용한 그룹핑 구현
-- Reference : blogs.msdn.com/b/craigfr/
-- Key word : grouping set cube rollup grouping_id grouping 그룹핑
***************************************************************************************/
-- 테이블 생성
-- drop table ttt;
CREATE TABLE ttt
( id INT NOT NULL
, sect NVARCHAR(5) NOT NULL
, val INT
);
-- 데이터 입력
INSERT INTO ttt
SELECT 1, 'A', 70 UNION ALL
SELECT 1, 'B', 80 UNION ALL
SELECT 1, 'C', 60 UNION ALL
SELECT 2, 'A', 90 UNION ALL
SELECT 2, 'B', 60 UNION ALL
SELECT 3, 'A', 60 UNION ALL
SELECT 3, 'B', 70 UNION ALL
SELECT 3, 'C', 80;
-- 입력 확인
SELECT * FROM ttt;
-- group by~ with rollup 사용
SELECT ISNULL(CAST(id AS NVARCHAR(5)), '총계') "id"
, CASE WHEN id IS NULL THEN '-'
ELSE ISNULL(sect, '소계')
END "sect"
, SUM(val) "val"
FROM ttt
GROUP BY id, sect
WITH ROLLUP;
-- group by~ grouping sets 사용
SELECT ISNULL(CAST(id AS NVARCHAR(5)), '총계') "id"
, CASE WHEN id IS NULL THEN '-'
ELSE ISNULL(sect, '소계')
END "sect"
, SUM(val) "val"
FROM ttt
GROUP BY GROUPING SETS((id, sect), (id), ())
-- group by~ rollup 사용
SELECT ISNULL(CAST(id AS NVARCHAR(5)), '총계') "id"
, CASE WHEN id IS NULL THEN '-'
ELSE ISNULL(sect, '소계')
END "sect"
, SUM(val) "val"
FROM ttt
GROUP BY ROLLUP(id, sect);
-- group by~ groupingset~ rollup 사용
SELECT ISNULL(CAST(id AS NVARCHAR(5)), '총계') "id"
, CASE WHEN id IS NULL THEN '-'
ELSE ISNULL(sect, '소계')
END "sect"
, SUM(val) "val"
FROM ttt
GROUP BY GROUPING SETS(ROLLUP(id, sect));
-- grouping_id함수 사용
SELECT ISNULL(CAST(id AS NVARCHAR(5)), '총계') "id"
, CASE WHEN id IS NULL THEN '-'
ELSE ISNULL(sect, '소계')
END "sect"
, SUM(val) "val"
, grouping_id(id) "grouping_id_id"
, grouping_id(id, sect) "grouping_id_id_sect"
, GROUPING(id) "grouping_id"
, GROUPING(sect) "grouping_sect"
FROM ttt
GROUP BY GROUPING SETS((id, sect), (id), ())