求一 SQL语句　，行转列解决方法

create table emi(诊断 varchar(16), 费用类别 int, 费用金额 decimal(5,2)) insert into emi select '白内障', 1, 30.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 105.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 14.00 union allselect '白内障', 3, 36.00 union allselect '白内障', 3, 122.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 38.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 37.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 121.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 4, 105.50 union allselect '膀胱恶性肿瘤', 1, 30.00 union allselect '膀胱恶性肿瘤', 1, 150.00 union allselect '膀胱恶性肿瘤', 1, 330.00 union allselect '膀胱恶性肿瘤', 1, 60.00 union allselect '膀胱恶性肿瘤', 1, 120.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 28.00 union allselect '膀胱恶性肿瘤', 2, 18.00 union allselect '膀胱恶性肿瘤', 2, 4.00 union allselect '膀胱恶性肿瘤', 3, 25.00 union allselect '膀胱恶性肿瘤', 3, 334.00 union allselect '膀胱恶性肿瘤', 4, 152.00 union allselect '膀胱恶性肿瘤', 4, 49.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 313.50 union allselect '膀胱恶性肿瘤', 5, 277.00select 诊断,isnull([1],0) '类别1金额合计',isnull([2],0) '类别2金额合计',isnull([3],0) '类别3金额合计',isnull([4],0) '类别4金额合计',isnull([5],0) '类别5金额合计'from emipivot(sum(费用金额) for 费用类别 in([1],[2],[3],[4],[5])) t诊断               类别1金额合计   类别2金额合计    类别3金额合计    类别4金额合计    类别5金额合计---------------- ------------- -------------- -------------- -------------- ---------------白内障              855.00         114.00           819.00        105.50          0.00膀胱恶性肿瘤        690.00          82.00           359.00        572.50          277.00(2 row(s) affected)
------解决方案--------------------
不知道对不对啊，只是能出来样式啊。

SQL code
if exists(select * from sysobjects where [name]='tb')drop table tbcreate table [tb]([ZD] varchar(20),[LB] varchar(4),[HZJE] money)goinsert tbselect '白内障','1','30.00'union select'白内障','1','75.00'union select'白内障','1','90.00'union select'白内障','1','75.00'union select'白内障','1','75.00'union select'白内障','1','75.00'union select'白内障','1','90.00'union select'白内障','1','75.00'union select'白内障','1','75.00'union select'白内障','1','90.00'union select'白内障','1','105.00'union select'白内障','2','12.00'union select'白内障','2','12.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','8.00'union select'白内障','2','8.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','14.00'union select'白内障','3','36.00'union select'白内障','3','122.00'union select'白内障','3','120.00'union select'白内障','3','120.00'union select'白内障','3','38.00'union select'白内障','3','35.00'union select'白内障','3','35.00'union select'白内障','3','37.00'union select'白内障','3','35.00'union select'白内障','3','121.00'union select'白内障','3','120.00'union select'白内障','4','105.50'union select'膀胱恶性肿瘤','1','30.00'union select'膀胱恶性肿瘤','1','150.00'union select'膀胱恶性肿瘤','1','330.00'union select'膀胱恶性肿瘤','1','60.00'union select'膀胱恶性肿瘤','1','120.00'union select'膀胱恶性肿瘤','1','16.00'union select'膀胱恶性肿瘤','1','16.00'union select'膀胱恶性肿瘤','2','28.00'union select'膀胱恶性肿瘤','2','18.00'union select'膀胱恶性肿瘤','2','4.00'union select'膀胱恶性肿瘤','3','25.00'union select'膀胱恶性肿瘤','3','334.00'union select'膀胱恶性肿瘤','4','152.00'union select'膀胱恶性肿瘤','4','49.00'union select'膀胱恶性肿瘤','4','29.00'union select'膀胱恶性肿瘤','4','29.00'union select'膀胱恶性肿瘤','4','313.50'union select'膀胱恶性肿瘤','5','277.00';--select * from tb;/*诊断 类别1金额合计 类别2金额合计 类别3金额合计   白内障  膀胱恶性肿瘤*/with a as(select zd ,lb,SUM(hzje)hj from tbgroup by ZD,LB),b as(select ZD,case when lb=1 then hj else 0 end lb_1,case when lb=2 then hj else 0 end lb_2,case when lb=3 then hj else 0 end lb_3,case when lb=4 then hj else 0 end lb_4,case when lb=5 then hj else 0 end lb_5from a)select  ZD,SUM(lb_1)类别1金额合计,SUM(lb_2)类别2金额合计,SUM(lb_3)类别3金额合计,SUM(lb_4)类别4金额合计,SUM(lb_5)类别5金额合计from b group by ZD