简单的表，分类汇总,该如何处理

简单的表，分类汇总
表结构：

CXRQ KB LB JE
20110419急诊科1300168.09
20110720专家门诊1602420.00
20110919专家门诊1502500.61
20110925大内科1600160.00
20110607普外科130213.38
20110810城北康1600160.00
20120106口腔科13012.70
20110426内科门诊13011594.24
20110504急诊科1616220.00
20111115专家门诊1601412.00
20110308内科门诊1601264.00
20110721口腔科13001.47
……………………………………
20110323体检中心1606170.00
20110616脑外科150539.00
20110919大内科1505283.50
20110310专家门诊14002480.08
20110212脑外科160425.00

表中：
CXRQ，是记录日期数据，长度8位，字符型，‘YYYYMMDD’，即年份4位，月份2位，日期2位
KB ，不用解释
LB ，是收费的类别，字符型，长度4位，包含在‘1001’至‘1999’
JE ，金额

现在要求是，生成一个分月汇总数据，形成如下表：

类别科1 科2 科3 科4 …… 合计
1001 汇总汇总汇总汇总 …… 汇总
1002 汇总汇总汇总汇总 …… 汇总
1003 汇总汇总汇总汇总 …… 汇总
……………………………………
1998 汇总汇总汇总汇总 …… 汇总
1999 汇总汇总汇总汇总 …… 汇总
合计汇总汇总汇总汇总 …… 汇总

谢谢！

[解决办法]

SQL code

if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([CXRQ] varchar(8),[KB] varchar(8),[LB] varchar(4),[JE] numeric(6,2))insert [tb]select '20110419','急诊科','1300',168.09 union allselect '20110720','专家门诊','1602',420.00 union allselect '20110919','专家门诊','1502',500.61 union allselect '20110925','大内科','1600',160.00 union allselect '20110607','普外科','1302',13.38 union allselect '20110810','城北康','1600',160.00 union allselect '20120106','口腔科','1301',2.70 union allselect '20110426','内科门诊','1301',1594.24 union allselect '20110504','急诊科','1616',220.00 union allselect '20111115','专家门诊','1601',412.00 union allselect '20110308','内科门诊','1601',264.00 union allselect '20110721','口腔科','1300',1.47 union allselect '20110323','体检中心','1606',170.00 union allselect '20110616','脑外科','1505',39.00 union allselect '20110919','大内科','1505',283.50 union allselect '20110310','专家门诊','1400',2480.08 union allselect '20110212','脑外科','1604',25.00go-->数据查询：declare @sql varchar(8000)select @sql=isnull(@sql+',','')+'sum(case when kb='''+kb+''' then je else 0 end) as ['+kb+']'from (select distinct kb from tb) texec ('select isnull(cxrq,''合计'') as [类别],' +@sql +',sum(je) as [合计] from tb group by cxrq with rollup' )/**类别城北康大内科急诊科口腔科内科门诊脑外科普外科体检中心专家门诊合计-------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------20110212 0.00 0.00 0.00 0.00 0.00 25.00 0.00 0.00 0.00 25.0020110308 0.00 0.00 0.00 0.00 264.00 0.00 0.00 0.00 0.00 264.0020110310 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2480.08 2480.0820110323 0.00 0.00 0.00 0.00 0.00 0.00 0.00 170.00 0.00 170.0020110419 0.00 0.00 168.09 0.00 0.00 0.00 0.00 0.00 0.00 168.0920110426 0.00 0.00 0.00 0.00 1594.24 0.00 0.00 0.00 0.00 1594.2420110504 0.00 0.00 220.00 0.00 0.00 0.00 0.00 0.00 0.00 220.0020110607 0.00 0.00 0.00 0.00 0.00 0.00 13.38 0.00 0.00 13.3820110616 0.00 0.00 0.00 0.00 0.00 39.00 0.00 0.00 0.00 39.0020110720 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 420.00 420.0020110721 0.00 0.00 0.00 1.47 0.00 0.00 0.00 0.00 0.00 1.4720110810 160.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 160.0020110919 0.00 283.50 0.00 0.00 0.00 0.00 0.00 0.00 500.61 784.1120110925 0.00 160.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 160.0020111115 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 412.00 412.0020120106 0.00 0.00 0.00 2.70 0.00 0.00 0.00 0.00 0.00 2.70合计 160.00 443.50 388.09 4.17 1858.24 64.00 13.38 170.00 3812.69 6914.07(17 行受影响)**/

[解决办法]
分月怎么显示？每个月每个类别显示一行？

SQL code

-->数据查询：declare @sql varchar(8000)select @sql=isnull(@sql+',','')+'sum(case when kb='''+kb+''' then je else 0 end) as ['+kb+']'from (select distinct kb from tb) texec ('select isnull(lb,''合计'') as [类别],' +@sql +',sum(je) as [合计] from tb group by lb with rollup' )/**类别城北康大内科急诊科口腔科内科门诊脑外科普外科体检中心专家门诊合计---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------1300 0.00 0.00 168.09 1.47 0.00 0.00 0.00 0.00 0.00 169.561301 0.00 0.00 0.00 2.70 1594.24 0.00 0.00 0.00 0.00 1596.941302 0.00 0.00 0.00 0.00 0.00 0.00 13.38 0.00 0.00 13.381400 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2480.08 2480.081502 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 500.61 500.611505 0.00 283.50 0.00 0.00 0.00 39.00 0.00 0.00 0.00 322.501600 160.00 160.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 320.001601 0.00 0.00 0.00 0.00 264.00 0.00 0.00 0.00 412.00 676.001602 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 420.00 420.001604 0.00 0.00 0.00 0.00 0.00 25.00 0.00 0.00 0.00 25.001606 0.00 0.00 0.00 0.00 0.00 0.00 0.00 170.00 0.00 170.001616 0.00 0.00 220.00 0.00 0.00 0.00 0.00 0.00 0.00 220.00合计 160.00 443.50 388.09 4.17 1858.24 64.00 13.38 170.00 3812.69 6914.07(13 行受影响)**/

简单的表分类汇总,该如何处理

热点推荐