--希望大家互相启发，找到更通用、自动化的方法!
--下面是我的方法
drop procedure test_proc
go
create procedure test_proc
as
begin
create table #result
(
col1 char(64),
col2 char(64),
xm char(128),
je00 numeric(19,6),
je01 numeric(19,6),
je02 numeric(19,6),
je03 numeric(19,6),
je04 numeric(19,6),
je05 numeric(19,6),
je06 numeric(19,6),
je07 numeric(19,6),
je08 numeric(19,6),
je09 numeric(19,6),
je10 numeric(19,6),
je11 numeric(19,6),
je12 numeric(19,6)
) insert into #result
select
case when (grouping(col1)=1) then ' 总计' else isnull(rtrim(col1), '') end as col1,
case when (grouping(col1)=0) AND (grouping(col2)=1) then ' 合计' else isnull(rtrim(col2), '') end as col2,
case
when (grouping(col1)=1) then '总计'
when (grouping(col1)=0) AND (grouping(col2)=1) then ''+isnull(rtrim(col1), '')
when (grouping(col1)=0) AND (grouping(col2)=0) then ' '+isnull(rtrim(col2), '')
else ''
end as xm, sum(je) as je00,
sum(case when yf_id = 1 then je else 0 end) as je01,
sum(case when yf_id = 2 then je else 0 end) as je02,
sum(case when yf_id = 3 then je else 0 end) as je03,
sum(case when yf_id = 4 then je else 0 end) as je04,
sum(case when yf_id = 5 then je else 0 end) as je05,
sum(case when yf_id = 6 then je else 0 end) as je06,
sum(case when yf_id = 7 then je else 0 end) as je07,
sum(case when yf_id = 8 then je else 0 end) as je08,
sum(case when yf_id = 9 then je else 0 end) as je09,
sum(case when yf_id = 10 then je else 0 end) as je10,
sum(case when yf_id = 11 then je else 0 end) as je11,
sum(case when yf_id = 12 then je else 0 end) as je12
from test
group by col1, col2 with rollup
order by col1, col2 select xm, je00, je01, je02, je03, je04, je05, je06, je07, je08, je09, je10, je11, je12
from #result
order by col1, col2
end
go
--下面是我的方法
drop procedure test_proc
go
create procedure test_proc
as
begin
create table #result
(
col1 char(64),
col2 char(64),
xm char(128),
je00 numeric(19,6),
je01 numeric(19,6),
je02 numeric(19,6),
je03 numeric(19,6),
je04 numeric(19,6),
je05 numeric(19,6),
je06 numeric(19,6),
je07 numeric(19,6),
je08 numeric(19,6),
je09 numeric(19,6),
je10 numeric(19,6),
je11 numeric(19,6),
je12 numeric(19,6)
) insert into #result
select
case when (grouping(col1)=1) then ' 总计' else isnull(rtrim(col1), '') end as col1,
case when (grouping(col1)=0) AND (grouping(col2)=1) then ' 合计' else isnull(rtrim(col2), '') end as col2,
case
when (grouping(col1)=1) then '总计'
when (grouping(col1)=0) AND (grouping(col2)=1) then ''+isnull(rtrim(col1), '')
when (grouping(col1)=0) AND (grouping(col2)=0) then ' '+isnull(rtrim(col2), '')
else ''
end as xm, sum(je) as je00,
sum(case when yf_id = 1 then je else 0 end) as je01,
sum(case when yf_id = 2 then je else 0 end) as je02,
sum(case when yf_id = 3 then je else 0 end) as je03,
sum(case when yf_id = 4 then je else 0 end) as je04,
sum(case when yf_id = 5 then je else 0 end) as je05,
sum(case when yf_id = 6 then je else 0 end) as je06,
sum(case when yf_id = 7 then je else 0 end) as je07,
sum(case when yf_id = 8 then je else 0 end) as je08,
sum(case when yf_id = 9 then je else 0 end) as je09,
sum(case when yf_id = 10 then je else 0 end) as je10,
sum(case when yf_id = 11 then je else 0 end) as je11,
sum(case when yf_id = 12 then je else 0 end) as je12
from test
group by col1, col2 with rollup
order by col1, col2 select xm, je00, je01, je02, je03, je04, je05, je06, je07, je08, je09, je10, je11, je12
from #result
order by col1, col2
end
go
[交流]行列转换
col1 char(16) not null, --单位
col2 char(16) not null, --科目
yf_id int not null, --月份
je numeric(19,6) not null --金额
)
insert into test
select '单位01','科目01',5,12
union all select '单位01','科目02',9,23
union all select '单位01','科目02',2,34
union all select '单位01','科目04',6,45
union all select '单位02','科目02',1,56
union all select '单位02','科目03',8,67
union all select '单位02','科目06',10,78
union all select '单位02','科目05',11,89
union all select '单位01','科目04',3,33
union all select '单位02','科目02',4,44
union all select '单位02','科目03',7,55
union all select '单位02','科目03',12,99
goselect case grouping(col1) when 1 then '总计'
else case grouping(col2) when 1 then col1
else space(4)+col2 end end
,合计=sum(je)
,[1月]=sum(case yf_id when 1 then je else 0 end)
,[2月]=sum(case yf_id when 2 then je else 0 end)
,[3月]=sum(case yf_id when 3 then je else 0 end)
,[4月]=sum(case yf_id when 4 then je else 0 end)
,[5月]=sum(case yf_id when 5 then je else 0 end)
,[6月]=sum(case yf_id when 6 then je else 0 end)
,[7月]=sum(case yf_id when 7 then je else 0 end)
,[8月]=sum(case yf_id when 8 then je else 0 end)
,[9月]=sum(case yf_id when 9 then je else 0 end)
,[10月]=sum(case yf_id when 10 then je else 0 end)
,[11月]=sum(case yf_id when 11 then je else 0 end)
,[12月]=sum(case yf_id when 12 then je else 0 end)
from test
group by col1,col2 with rollup
order by grouping(col1) desc,col1,grouping(col2) desc
,col2go
drop table test/*--测试结果
合计 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
-------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
总计 635.000000 56.000000 34.000000 33.000000 44.000000 12.000000 45.000000 55.000000 67.000000 23.000000 78.000000 89.000000 99.000000
单位01 147.000000 .000000 34.000000 33.000000 .000000 12.000000 45.000000 .000000 .000000 23.000000 .000000 .000000 .000000
科目01 12.000000 .000000 .000000 .000000 .000000 12.000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000
科目02 57.000000 .000000 34.000000 .000000 .000000 .000000 .000000 .000000 .000000 23.000000 .000000 .000000 .000000
科目04 78.000000 .000000 .000000 33.000000 .000000 .000000 45.000000 .000000 .000000 .000000 .000000 .000000 .000000
单位02 488.000000 56.000000 .000000 .000000 44.000000 .000000 .000000 55.000000 67.000000 .000000 78.000000 89.000000 99.000000
科目02 100.000000 56.000000 .000000 .000000 44.000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000
科目03 221.000000 .000000 .000000 .000000 .000000 .000000 .000000 55.000000 67.000000 .000000 .000000 .000000 99.000000
科目05 89.000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 89.000000 .000000
科目06 78.000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 .000000 78.000000 .000000 .000000（所影响的行数为 10 行）
--*/
--我做的系统中有很多此类存储过程
--我试图总结其中的共同点，找到一种显著降低此类问题工作量的方法drop procedure tzjh_print_proc
go
create procedure tzjh_print_proc(@tzjh_id int, @jd_id int)
with encryption
as
begin
create table #tzjh_1
(
dl_name char(64),
dw_name char(64),
jj_name char(64),
je numeric(19,6),
jjlb_code char(16),
bnsn_code char(16)
) insert into #tzjh_1
select
rtrim(dl_code)+' '+rtrim(dl_name),
rtrim(dl_code)+rtrim(dw_code)+' '+rtrim(dw_name),
rtrim(dl_code)+rtrim(dw_code)+'-'+rtrim(jj_code)+' '+rtrim(jj_name),
je/@jd_id,
jjlb_code,
bnsn_code
from tzjh_v
where tzjh_id=@tzjh_id select
case
when (grouping(dl_name)=1) then '总计'
else
case
when (grouping(dw_name)=1) then isnull(rtrim(dl_name), '')
else
case
when (grouping(jj_name)=1) then space(4)+isnull(rtrim(dw_name), '')
else space(4*2)+isnull(rtrim(jj_name), '')
end
end
end as xm, sum(case when jjlb_code in ('1','7','8','4','5') then je else 0 end) as je01,
sum(case when jjlb_code = '1' then je else 0 end) as je02,
sum(case when jjlb_code = '7' then je else 0 end) as je03,
sum(case when jjlb_code = '8' then je else 0 end) as je04,
sum(case when jjlb_code = '4' then je else 0 end) as je05,
sum(case when jjlb_code = '5' then je else 0 end) as je06,
sum(case when jjlb_code in ('3','6','2') then je else 0 end) as je07,
sum(case when jjlb_code in ('3','6','2') and bnsn_code='1' then je else 0 end) as je08,
sum(case when jjlb_code in ('3') and bnsn_code='1' then je else 0 end) as je09,
sum(case when jjlb_code in ('6') and bnsn_code='1' then je else 0 end) as je10,
sum(case when jjlb_code in ('2') and bnsn_code='1' then je else 0 end) as je11,
sum(case when jjlb_code in ('3','6','2') and bnsn_code='2' then je else 0 end) as je12,
sum(case when jjlb_code in ('3') and bnsn_code='2' then je else 0 end) as je13,
sum(case when jjlb_code in ('6') and bnsn_code='2' then je else 0 end) as je14,
sum(case when jjlb_code in ('2') and bnsn_code='2' then je else 0 end) as je15
from #tzjh_1
group by dl_name, dw_name, jj_name with rollup
order by
grouping(dl_name) desc, dl_name,
grouping(dw_name) desc, dw_name,
grouping(jj_name) desc, jj_name
end
go