SQL & PL/SQL

Hello There,

I have a requirement to group all child records under its parent record. The data is present in the same table. There is a relation between the parent and the child records as below.

Could you please assist how the output can be derived using SQL query?:

1. Without the column Total Quantity

2. With the column Total Quantity     (Refer output section below)

A. Below data set records query:

WITH DataSet AS

     (SELECT 6001 ORG_ID, 6001 PARTY_ID, 100  PARTY_NUMBER, 'Parent ABC' PARTY_NAME, 'Parent' ATTRIBUTE1, NULL ATTRIBUTE2, 1 QUANTITY FROM dual UNION ALL

      SELECT 6002 ORG_ID, 6002 PARTY_ID, 1601 PARTY_NUMBER, 'Child 1' PARTY_NAME, 'Child' ATTRIBUTE1, 100 ATTRIBUTE2, 2 QUANTITY FROM dual UNION ALL

      SELECT 6003 ORG_ID, 6003 PARTY_ID, 1602 PARTY_NUMBER, 'Child 2' PARTY_NAME, 'Child' ATTRIBUTE1, 100 ATTRIBUTE2, 3 QUANTITY FROM dual UNION ALL

      SELECT 6004 ORG_ID, 6004 PARTY_ID, 1603 PARTY_NUMBER, 'Child 3' PARTY_NAME, 'Child' ATTRIBUTE1, 100 ATTRIBUTE2, 4 QUANTITY FROM dual UNION ALL

      SELECT 6005 ORG_ID, 6005 PARTY_ID, 1604 PARTY_NUMBER, 'Child 4' PARTY_NAME, 'Child' ATTRIBUTE1, 100 ATTRIBUTE2, 5 QUANTITY FROM dual UNION ALL

      SELECT 6006 ORG_ID, 6006 PARTY_ID, 1605 PARTY_NUMBER, 'Child 5' PARTY_NAME, 'Child' ATTRIBUTE1, 100 ATTRIBUTE2, 6 QUANTITY FROM dual UNION ALL

      SELECT 6007 ORG_ID, 6007 PARTY_ID, 1606 PARTY_NUMBER, 'Child 6' PARTY_NAME, 'Child' ATTRIBUTE1, 100 ATTRIBUTE2, 7 QUANTITY FROM dual UNION ALL

      SELECT 7001 ORG_ID, 7001 PARTY_ID, 200  PARTY_NUMBER, 'Parent XYZ' PARTY_NAME, 'Parent' ATTRIBUTE1, NULL ATTRIBUTE2, 10 QUANTITY FROM dual UNION ALL

      SELECT 7002 ORG_ID, 7002 PARTY_ID, 1701 PARTY_NUMBER, 'Child 1' PARTY_NAME, 'Child' ATTRIBUTE1, 200 ATTRIBUTE2, 11 QUANTITY FROM dual UNION ALL

      SELECT 7003 ORG_ID, 7003 PARTY_ID, 1702 PARTY_NUMBER, 'Child 2' PARTY_NAME, 'Child' ATTRIBUTE1, 200 ATTRIBUTE2, 12 QUANTITY FROM dual UNION ALL

      SELECT 7004 ORG_ID, 7004 PARTY_ID, 1703 PARTY_NUMBER, 'Child 3' PARTY_NAME, 'Child' ATTRIBUTE1, 200 ATTRIBUTE2, 13 QUANTITY FROM dual UNION ALL

      SELECT 7005 ORG_ID, 7005 PARTY_ID, 1704 PARTY_NUMBER, 'Child 4' PARTY_NAME, 'Child' ATTRIBUTE1, 200 ATTRIBUTE2, 14 QUANTITY FROM dual UNION ALL

      SELECT 7006 ORG_ID, 7006 PARTY_ID, 1705 PARTY_NUMBER, 'Child 5' PARTY_NAME, 'Child' ATTRIBUTE1, 200 ATTRIBUTE2, 15 QUANTITY FROM dual UNION ALL

      SELECT 7007 ORG_ID, 7007 PARTY_ID, 1706 PARTY_NUMBER, 'Child 6' PARTY_NAME, 'Child' ATTRIBUTE1, 200 ATTRIBUTE2, 16 QUANTITY FROM dual

      )

B. Common Identifiers:

Identifying Parent records: Attribute1 = 'Parent' AND Attribute2 Is Null

Identifying Child records: Attribute1 = 'Child'      AND Child record's Attribute2 = Parent record's Party_Number

C. The desired output is as follows:

Registry             = Party_Number

Parent Registry = Attribute2

Description         = Party_Name

Registry Type    = Attribute1

Quantity              = Quantity

Total Quantity      = Sum of Quantity of all Child record's for that Parent record (can be displayed at the Parent record or at the last child record for that parent record)

Many thanks and look forward to hearing from you.

Regards,

Don