Instance description
Data Table Structure (primary key in red) ==>
Master table: test_part_cost_tab (item table)
Part_no varchar2 (20)
Part_name varchar2 (50)
Slave table: test_part_cost_dt_tab (item cost table)
Part_no varchar2 (10)
Cost_id varchar2 (5)
Cost number
Data ==>
Master table data:
Part_no part_name
1 1000 name1000
2 1001 name1001
Slave table:
Part_no cost_id cost
1 1000 100 1.1
2 1000 200 1.2
3 1000 300 1.3
4 1000 321 1.321
5 1001 100 2.1
crosstab chart ==>< br> SQL statement result
part_no part_name cost_100 cost_200 cost_300 cost_321
1 1000 name1000 1.1 1.2 1.3
2 1.321 name1001 2.1 0 0 0
Specific cross-SQL statement Syntax:
Select a. part_no, A. part_name,
-- Sum (B. Cost)
Sum (case when B. cost_id = '000000' then B. Cost else 0 end) as cost_100,
Sum (case when B. cost_id = '000000' then B. Cost else 0 end) as cost_200,
Sum (case when B. cost_id = '000000' then B. Cost else 0 end) as cost_300,
Sum (case when B. cost_id = '000000' 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: if there is data in the master table and there is no data in the slave table, there will be no corresponding data after the cross
The solution is to use an external connection in the where condition.
Where a. part_no = B. part_no (+)
References:
Http://www.cnblogs.com/iouniuniu/archive/2004/04/05/5238.aspx => the syntax of this article is different from the syntax of this Article
Http://it.icxo.com/htmlnews/2004/11/11/454293.htm
Http://www.itpub.net/176727.html