#title 소계/합계만들기

{{{
create table #test(codel char(4),codes char(3),월 varchar(4),가격 int)

insert #test values('0001','a01','1월',100)
insert #test values('0001','a01','2월',100)
insert #test values('0001','a02','2월',200)
insert #test values('0001','a03','3월',300)
insert #test values('0002','a04','1월',300)
insert #test values('0002','a05','2월',200)
insert #test values('0003','a06','1월',100)
insert #test values('0003','a06','3월',500)
insert #test values('0003','a07','3월',700)


select
	isnull(code, '합계') code
,	isnull(convert(varchar(4), mon), '소계') code
,	[1월]
,	[2월]
,	[3월]
from(
	select
		case when b.seq in (2, 3) then code end code
	,	case when b.seq = 2 then mon end mon
	,	sum([1월]) [1월]
	,	sum([2월]) [2월]
	,	sum([3월]) [3월]
	from (
		select codel code,
			   codes 'mon',
			   sum(case 월 when '1월' then 가격 else 0 end) [1월],
			   sum(case 월 when '2월' then 가격 else 0 end) [2월],
			   sum(case 월 when '3월' then 가격 else 0 end) [3월]
		  from #test
		 group by codel,codes) a 
			cross join (select 1 seq union all select 2 union all select 3) b
	group by 
		case when b.seq in (2, 3) then code end
	,	case when b.seq = 2 then mon end
) t
order by
	case when code is null then '9999' else code end
,	case when mon is null then 'zzz' else mon end
/*
code code          1월          2월          3월
---- ---- ----------- ----------- -----------
0001 A01          100         100           0
0001 A02            0         200           0
0001 A03            0           0         300
0001 소계           100         300         300
0002 A04          300           0           0
0002 A05            0         200           0
0002 소계           300         200           0
0003 A06          100           0         500
0003 A07            0           0         700
0003 소계           100           0        1200
합계   소계           500         500        1500
*/
}}}