Cross-SQL statement writing in Oracle

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.