SELECT T1.科室,
(SELECT SUM(费用)
FROM T
WHERE T.科室 = T1.科室
AND T.费用类别 = '药费') "药费",
(SELECT SUM(费用)
FROM T
WHERE T.科室 = T1.科室
AND T.费用类别 = '耗材') "耗材",
(SELECT SUM(费用)
FROM T
WHERE T.科室 = T1.科室
AND T.费用类别 NOT IN ('药费', '耗材')) "其他"
FROM (SELECT DISTINCT 科室 FROM T) T1
(SELECT SUM(费用)
FROM T
WHERE T.科室 = T1.科室
AND T.费用类别 = '药费') "药费",
(SELECT SUM(费用)
FROM T
WHERE T.科室 = T1.科室
AND T.费用类别 = '耗材') "耗材",
(SELECT SUM(费用)
FROM T
WHERE T.科室 = T1.科室
AND T.费用类别 NOT IN ('药费', '耗材')) "其他"
FROM (SELECT DISTINCT 科室 FROM T) T1
SELECT 科室,
SUM(CASE WHEN 费用类别 = '药费' THEN 费用 ELSE 0 END ) '药费',
SUM(CASE WHEN 费用类别 = '耗材' THEN 费用 ELSE 0 END ) '耗材',
SUM(CASE WHEN 费用类别 NOT IN ('药费','耗材') THEN 费用 ELSE 0 END ) '其它'
FROM TB
GROUP BY 科室
ORDER BY 科室
SELECT 'A' 科室,'药费' 费用类别,10 费用 FROM dual UNION ALL
SELECT 'B' ,'药费' ,20 FROM dual UNION ALL
SELECT 'C' ,'药费' ,20 FROM dual UNION ALL
SELECT 'A' ,'耗材' ,10 FROM dual UNION ALL
SELECT 'B' ,'耗材' ,20 FROM dual UNION ALL
SELECT 'C' ,'耗材' ,20 FROM dual UNION ALL
SELECT 'A' ,'麻醉' ,10 FROM dual UNION ALL
SELECT 'B' ,'麻醉' ,20 FROM dual UNION ALL
SELECT 'C' ,'麻醉' ,20 FROM dual UNION ALL
SELECT 'C' ,'手术' ,20 FROM dual UNION ALL
SELECT 'C' ,'药费' ,10 FROM dual
)
SELECT 科室,
SUM(DECODE(费用类别, '药费', 费用, 0)) 药费,
SUM(DECODE(费用类别, '耗材', 费用, 0)) 药费,
SUM(DECODE(费用类别, '药费' ,0, '耗材', 0, 费用)) 其他
FROM TEST
GROUP BY 科室
SELECT tb.科室,SUM(费用) as 药材 from tb where tb.费用类别='药费' group by tb.科室) A
,( SELECT tb.科室,SUM(费用) as 耗材 from tb where tb.费用类别='耗材' group by tb.科室) B
,( SELECT tb.科室,SUM(费用) as 其他 from tb where tb.费用类别 not in ('药费','耗材') group by tb.科室) C
where A.科室=B.科室 and B.科室=C.科室