select distinct q2.ks ,q2.检查,q3.西药 from
(select distinct ks ,mc,sum(jg) from wxxtest group by ks,mc) q1
,(select '内科' ks,sum(jg) 检查 from wxxtest where mc='检查') q2
,(select '内科' ks,sum(jg) 西药 from wxxtest where mc='西药') q3
where q1.ks=q2.ks(+)
and q1.ks=q3.ks(+)
and (q1.mc='检查' or q1.mc='西药')
SELECT *
FROM (SELECT 科室,
sum(decode(药品,西药,费用)) 西药,
sum(decode(药品,中药,费用)) 中药,
sum(decode(药品,检查,费用)) 检查0,
sum(decode(药品,手术,费用)) 手术
sum(decode(药品,化验,费用)) 化验
FROM your_table
GROUP BY 科室)
ORDER BY 1;