具體的交叉SQL語句寫法:
select a.part_no,a.part_name,
--sum(b.cost)
sum(case when b.cost_id = '100' then b.cost else 0 end) as cost_100,
sum(case when b.cost_id = '200' then b.cost else 0 end) as cost_200,
sum(case when b.cost_id = '300' then b.cost else 0 end) as cost_300,
sum(case when b.cost_id = '321' then b.cost else 0 end) as cost_321
from test_part_cost_tab a,test_part_cost_dt_tab b
where a.part_no = b.part_no
group by a.part_no,a.part_name
PS: 若主表有資料,從表沒有資料時,交叉後會沒有相應的資料
解決辦法是在WHERE條件裡用外串連
where a.part_no = b.part_no(+)
