pubs db에서 테스트 한 겁니다.

이것과 유사한 방식으로 하면 되지 않을까요.

날짜를 데이터타입에 따라 약간 달라질 수 있겠지만..

아래쪽은 연도가 주어졌을때 상점,분기별계를

크로스텝형태로 나열해 본 것입니다.

select a.stor_id,
       a.yyyy,
       a.quater,      
       isnull(sum(a.qty), 0)
from (
     select a.stor_id,
            a.yyyy,
            case when a.mm between  1 and  3 then '1분기'
                 when a.mm between  4 and  6 then '2분기'
                 when a.mm between  7 and  9 then '3분기'
                 when a.mm between 10 and 12 then '4분기'
            end  quater,      
            sum(a.qty)      qty 
     from (
          select stor_id         stor_id,
                 year(ord_date)  yyyy,
                 month(ord_date) mm,
                 sum(qty)        qty
          from sales
          group by stor_id,
                   year(ord_date),
                   month(ord_date)
          ) a
     group by a.stor_id, a.yyyy, a.mm
     ) a
group by a.stor_id, a.yyyy, a.quater
order by a.stor_id, a.yyyy, a.quater

select isnull(a.stor_id, '분기계') '상점',
       isnull(sum(case a.quater when '1분기' then a.qty end), 0) '1분기',
       isnull(sum(case a.quater when '2분기' then a.qty end), 0) '2분기',
       isnull(sum(case a.quater when '3분기' then a.qty end), 0) '3분기',
       isnull(sum(case a.quater when '4분기' then a.qty end), 0) '4분기',
       isnull(sum(a.qty), 0)                                     '상점계'
from (
      select a.stor_id,
             case when a.mm between  1 and  3 then '1분기'
                  when a.mm between  4 and  6 then '2분기'
                  when a.mm between  7 and  9 then '3분기'
                  when a.mm between 10 and 12 then '4분기'
             end  quater,      
             sum(a.qty)      qty 
      from (
           select stor_id         stor_id,
                  month(ord_date) mm,
                  sum(qty)        qty
           from sales
           where ord_date between cast('1993-01-01' as datetime) and cast('1993-12-31' as datetime)
           group by stor_id,
                    year(ord_date),
                    month(ord_date)
           ) a
      group by a.stor_id, a.mm
      ) a
group by a.stor_id with rollup
order by isnull(a.stor_id, '총계')