70세 코딩

조직구조나 BOM 과 같이 계층구조로 된 데이터에 대하여 쉽게 쿼리를 구현할 수 있도록 해주는 것이 CTE,
그중에서도 재귀적 CTE 이다.

with cteTest(uppercode, mycode, levelno, groupcode) as (        select uppercode, mycode, 0, mycode from test1 where uppercode = 4        union all        select a.uppercode, a.mycode, b.levelno+1, b.groupcode  from test1 a join cteTest b on a.uppercode = b.mycode ) select        a.groupcode as rootcode,        a.mycode as leafcode from cteTest a where        a.levelno = (select MAX(levelno) from cteTest b                                  where a.groupcode = b.groupcode)

마지막 쿼리는 계층구조의 최하위 레벨과 최상위코드(상위코드가 NULL) + 1인 레벨을 연결한 예이다.
(조직구조의 최하위 조직이 속한 최상위 조직을 알고자 하는 경우)

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

결과는 동일하다.