SQL서버 ROLLUP 사용

SQL서버 에서 제공되는 ROLLUP을 사용하면 그룹간의 소계 및 전체 합계를 쉽게 구할 수 있다.

create table test1
(id int,
 dept_code int,
 qty int)

insert into test1
 (id, dept_code, qty)
 select 4, 200, 1450
 union all
 select 6, 300, 1230
 union all
 select 3, 100, 3000
 union all
 select 1, 100, 1000
 union all
 select 5, 200, 2540
 union all
 select 2, 100, 2000

select
    case when dept_code is null and id is null then '합계'
         when id is null then '소계'
         else CAST(id as varchar(10)) 
    end as id,
    dept_code, SUM(qty) as qty
from 
    test1
group by 
  rollup (dept_code, id)

ROLLUP을 사용하지 않고 기존 ANSI Query를 사용하면 다음과 같이 할 수 있다.

select
       case b.gb when 1 then CAST(a.id as varchar(10))
                 when 2 then '소계'
                 when 3 then '합계'
       end as id,
       case when b.gb in (1,2) then a.dept_code end as dept_code,
       SUM(qty) as qty
from  
       test1 a
join
       (select 1 as gb union all select 2 union all select 3) b on 1=1
group by
       b.gb,
       case b.gb when 1 then CAST(a.id as varchar(10))
                 when 2 then '소계'
                 when 3 then '합계'
       end,
       case when b.gb in (1,2) then a.dept_code end
order by
       case when case when b.gb in (1,2) then a.dept_code end is null then 999
            else case when b.gb in (1,2) then a.dept_code end end, gb

결과는 동일하다.