34,588
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tbl]
go
if object_id('[tbl]') is not null
drop table [tbl]
go
create table [tbl](
[时间] int,
[类别] varchar(11),
[肛肠科] numeric(7,2),
[肿瘤科] numeric(7,2),
[普外科] numeric(8,2),
[血液透析室] numeric(8,2),
[骨伤一科] numeric(8,2)
)
go
insert [tbl]
select 201201,'B超检查F',0.00,0.00,0.00,0.00,80.00 union all
select 201201,'CT检查F',0.00,840.00,8800.00,0.00,6820.00 union all
select 201201,'病理F',480.00,120.00,5260.00,0.00,600.00 union all
select 201201,'材料费H',516.00,1463.00,44548.00,0.00,91622.00 union all
select 201201,'彩超F',140.00,640.00,6600.00,80.00,420.00 union all
select 201201,'床位费A',1860.00,1770.00,14140.00,0.00,19786.00 union all
select 201201,'放射费F',88.00,616.00,5890.00,0.00,17252.00 union all
select 201201,'护理费G',1608.00,730.00,11410.00,0.00,17935.50 union all
select 201201,'甲类草药费D',312.10,269.49,803.03,228.23,74.95 union all
select 201201,'甲类成药费C',0.00,807.45,417.78,24.61,5480.18 union all
select 201201,'甲类西药费B',1890.01,8462.22,32703.55,6119.16,27108.40 union all
select 201201,'检查费F',42.00,38.00,4288.50,110.00,5691.50 union all
select 201201,'检验费F',4621.50,5040.00,31591.50,944.00,23278.50 union all
select 201201,'接生费J',0.00,0.00,0.00,0.00,0.00 union all
select 201201,'救护车费J',0.00,0.00,1200.00,300.00,300.00 union all
select 201201,'麻醉费J',560.00,0.00,21788.00,0.00,19086.00 union all
select 201201,'其他自费K',610.00,45.00,4447.50,0.00,6760.00 union all
select 201201,'手术费J',7700.00,0.00,39025.00,385.00,38870.00 union all
select 201201,'输血费I',0.00,0.00,0.00,106.00,0.00 union all
select 201201,'胃镜F',170.00,0.00,850.00,0.00,0.00 union all
select 201201,'吸氧费I',139.00,0.00,2839.50,0.00,903.00 union all
select 201201,'心电图F',525.00,400.00,1975.00,0.00,2325.00 union all
select 201201,'乙类成药费C',20.88,962.64,8212.69,745.88,33639.42 union all
select 201201,'乙类西药费B',15732.08,67255.30,193432.18,48479.51,223115.47 union all
select 201201,'针灸推拿I',0.00,0.00,0.00,0.00,630.00 union all
select 201201,'诊断费E',234.00,168.00,1700.00,0.00,2716.00 union all
select 201201,'治疗费I',1014.00,548.00,7166.00,266141.00,11960.50 union all
select 201201,'注射费I',1149.00,1824.00,14326.00,1182.00,27880.00 union all
select 201201,'自费材料费H',190.50,117.00,1611.00,0.00,1970.00 union all
select 201201,'自费草药费D',0.00,0.00,0.00,0.00,0.00 union all
select 201201,'自费成药费C',0.00,0.00,0.00,0.00,1424.16 union all
select 201201,'自费西药费B',936.12,8.84,4105.30,3472.68,18337.73 union all
select 201202,'B超检查F',0.00,0.00,20.00,0.00,0.00 union all
select 201202,'CT检查F',0.00,1380.00,4560.00,160.00,10280.00 union all
select 201202,'DR检查F',0.00,0.00,0.00,0.00,0.00 union all
select 201202,'病理F',2160.00,0.00,5600.00,0.00,840.00 union all
select 201202,'材料费H',687.50,3899.00,36854.00,118.00,110182.50 union all
select 201202,'彩超F',190.00,780.00,6300.00,0.00,530.00 union all
select 201202,'床位费A',2715.00,3525.00,14525.00,0.00,22236.00 union all
select 201202,'放射费F',176.00,528.00,4876.00,88.00,20052.00 union all
select 201202,'护理费G',2049.00,1801.00,11955.00,0.00,27278.00 union all
select 201202,'甲类草药费D',254.56,140.30,728.05,188.35,546.63 union all
select 201202,'甲类成药费C',0.00,54.63,699.36,194.12,5432.20 union all
select 201202,'甲类西药费B',5344.13,21764.64,34534.26,6275.96,32048.60 union all
select 201202,'检查费F',52.00,300.00,8044.70,623.00,8483.00 union all
select 201202,'检验费F',5948.50,11255.50,27029.50,55.00,22600.00 union all
select 201202,'接生费J',0.00,0.00,0.00,0.00,0.00
declare @str varchar(max)
set @str=''
select @str=@str+','+'sum('+name+') as '+name from syscolumns where id=object_id('tbl')
and name not in('时间','类别')
exec('select 时间,right(类别,1) as 类别'+@str+' from tbl group by 时间,right(类别,1)
order by 1')
/*
时间 类别 肛肠科 肿瘤科 普外科 血液透析室 骨伤一科
201201 A 1860.00 1770.00 14140.00 0.00 19786.00
201201 B 18558.21 75726.36 230241.03 58071.35 268561.60
201201 C 20.88 1770.09 8630.47 770.49 40543.76
201201 D 312.10 269.49 803.03 228.23 74.95
201201 E 234.00 168.00 1700.00 0.00 2716.00
201201 F 6066.50 7694.00 65255.00 1134.00 56467.00
201201 G 1608.00 730.00 11410.00 0.00 17935.50
201201 H 706.50 1580.00 46159.00 0.00 93592.00
201201 I 2302.00 2372.00 24331.50 267429.00 41373.50
201201 J 8260.00 0.00 62013.00 685.00 58256.00
201201 K 610.00 45.00 4447.50 0.00 6760.00
201202 A 2715.00 3525.00 14525.00 0.00 22236.00
201202 B 5344.13 21764.64 34534.26 6275.96 32048.60
201202 C 0.00 54.63 699.36 194.12 5432.20
201202 D 254.56 140.30 728.05 188.35 546.63
201202 F 8526.50 14243.50 56430.20 926.00 62785.00
201202 G 2049.00 1801.00 11955.00 0.00 27278.00
201202 H 687.50 3899.00 36854.00 118.00 110182.50
201202 J 0.00 0.00 0.00 0.00 0.00
*/
不确定字段数,那就动态实现
with a as(
select 时间,right(类别,1),sum(a),sum(b).... from 表 group by 时间,right(类别,1);
union all
select 时间,right(类别,1),a,b,...from 表 group by 时间,right(类别,1)
);
select * from a order by right(类别,1),时间
select 时间,right(类别,1),sum(xx),sum(xx).... from 表 group by 时间,right(类别,1);