CODEWITHSUNDEEP
sqloracle-databaseunionunion-all
shockwave
shockwave

Reputation: 3262

SQL Merge rows instead of UNION ALL

I'm doing a UNION ALL to get the results as shows in the table below. This approach is causing to have unnecessary rows. The three columns DESK, SEGMENT and SUPERVISOR are independent and have no relationship.

Code

SELECT ID, DESK, '' as SEGMENT, '' as SUPERVISOR FROM myTable1 
UNION ALL 
SELECT ID, '' AS DESK, SEGMENT, '' as SUPERVISOR FROM myTable2 
UNION ALL 
SELECT ID, '' AS DESK, '' as SEGMENT, SUPERVISOR FROM myTable3

Result:

+------+------------+---------+------------+
| ID   | DESK       | SEGMENT | SUPERVISOR | TOTAL ENTRIES
+------+------------+---------+------------+
| 4782 | OIL & GAS  |         |            |  23
+------+------------+---------+------------+
| 4782 | AUTOMOTIVE |         |            |  23
+------+------------+---------+------------+
| 4782 |            | GLOBAL  |            |  23
+------+------------+---------+------------+
| 4782 |            |         | DANIEL     |  23
+------+------------+---------+------------+
| 4782 |            |         | JAMES      |  23
+------+------------+---------+------------+

How can I query to get the below result?

Expected Result:

+------+------------+---------+------------+
| ID   | DESK       | SEGMENT | SUPERVISOR | TOTAL ENTRIES
+------+------------+---------+------------+
| 4782 | OIL & GAS  | GLOBAL  | DANIEL     |  23
+------+------------+---------+------------+
| 4782 | AUTOMOTIVE |         | JAMES      |  23
+------+------------+---------+------------+

Upvotes: 1

Views: 1207

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can use ROW_NUMBER() analytic function with partitioned by ID column along with FULL OUTER JOIN for those three tables like this :

SELECT NVL(NVL(t2.ID,t3.ID),t1.ID) AS ID, desk, segment, supervisor 
  FROM ( SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn FROM myTable1 t1 ) t1
  FULL JOIN ( SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn FROM myTable2 t2 ) t2
    ON t2.ID = t1.ID AND t2.rn = t1.rn
  FULL JOIN ( SELECT t3.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn FROM myTable3 t3 ) t3
    ON t3.ID = t1.ID AND t3.rn = t1.rn;


ID   DESK        SEGMENT  SUPERVISOR
---- ----------  -------  ----------
4782 AUTOMOTIVE  GLOBAL   JAMES
4782 OIL & GAS            DANIEL

Demo

P.S: I left ORDER BY 0 as ORDER BY option is mandatory for ROW_NUMBER(), you can replace zero with a proper column or identifier for you.

Upvotes: 1

EJ Egyed
EJ Egyed

Reputation: 6084

You can try a query like the one below. I don't know where the 23 is coming from so I did not factor it into the query, but if it is a column in one of the three tables, similar logic can be used to add it to the results.

Query

WITH
    table1 (id, desk)
    AS
        (SELECT 4782, 'OIL & GAS' FROM DUAL
         UNION ALL
         SELECT 4782, 'AUTOMOTIVE' FROM DUAL),
    table2 (id, segment) AS (SELECT 4782, 'GLOBAL' FROM DUAL),
    table3 (id, supervisor)
    AS
        (SELECT 4782, 'DANIEL' FROM DUAL
         UNION ALL
         SELECT 4782, 'JAMES' FROM DUAL)
SELECT *
  FROM (SELECT t1.id,
               CASE WHEN t1.desk = LAG (t1.desk) OVER (ORDER BY t1.desk) THEN NULL ELSE t1.desk END
                   AS desk,
               CASE
                   WHEN t2.segment = LAG (t2.segment) OVER (ORDER BY t2.segment) THEN NULL
                   ELSE t2.segment
               END
                   AS segment,
               CASE
                   WHEN t3.supervisor = LAG (t3.supervisor) OVER (ORDER BY t3.supervisor) THEN NULL
                   ELSE t3.supervisor
               END
                   AS supervisor
          FROM table1 t1, table2 t2, table3 t3
         WHERE t1.id = t2.id AND t1.id = t3.id)
 WHERE desk IS NOT NULL OR segment IS NOT NULL OR supervisor IS NOT NULL;

Result

     ID          DESK    SEGMENT    SUPERVISOR
_______ _____________ __________ _____________
   4782 AUTOMOTIVE    GLOBAL     DANIEL
   4782 OIL & GAS                JAMES

Upvotes: 0

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can try this:

SELECT table1.ID, table1.DESK, table2.SEGMENT, (select SUPERVISOR from (select SUPERVISOR, ROWNUM AS RN FROM table3) WHERE RN = 1) SUPERVISOR
FROM table1 JOIN table2 on table1.ID = table2.ID
WHERE table1.DESK = 'OIL & GAS'
UNION ALL
SELECT table1.ID, table1.DESK, null SEGMENT, (select SUPERVISOR from (select SUPERVISOR, ROWNUM AS RN FROM table3) WHERE RN = 2) SUPERVISOR
FROM table1 JOIN table2 on table1.ID = table2.ID
WHERE table1.DESK = 'AUTOMOTIVE'

enter image description here

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=c5594bb1d99579611d2669f6bab675a2

Upvotes: 0

Related Questions