Example statement
Data table structure (red is primary key) ==> Master table: test_part_cost_tab (material number information table) part_no varchar2 (20) part_name varchar2 (50)
From Table: Test_Part_cost_dt_tab (Medical Cost Table) Part_no varchar2 (10) COST_ID VARCHAR2 (5) COST NUMBER
Data ==> NAME1 1000 Name1000 2 1001 Name1001 From Table Information: Part_no COST_ID COST1 1000 100 1.1 2 1000 200 1.2 3 1000 300 1.34 1000 321 1.321 5 1001 100 2.1
Cross-information ==> SQL statement generated Results Part_no part_name cost_100 cost_200 cost_300 COST_3211 1000 Name1000 1.1 1.2 1.3 1.3212 1001 Name1001 2.1 0 0 0
Specific cross SQL statement writing: 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' TEN B.COST ELSE 0 END) AS COST_200, SUM (Case When B.COST_ID = '300' TEN B.COST ELSE 0 END) AS COST_300, SUM (Case When B.COST_ID = '321 'Ten B.Cost Else 0 End) AS COST_321
From test_part_cost_tab A, test_part_cost_dt_tab bwhere a.part_no = b.part_nogroup by a.part_no, a.part_name
PS: If there is information, when there is no information from the table, there will be no corresponding information solution after crossing, and there is an external connection where a.part_no = B.Part_no ( )
Reference: http://www.cnblogs.com/iouniUniu/archive/2004/04/05/5238.aspx ==> This text is different from the grammar of this article http://it.icxo.com/htmlnews/ 2004/11/11 / 454293.htm http://www.itpub.net/176727.html
