分组求和的SQL

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