SQL Merge rows instead of UNION ALL

Question

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
+------+------------+---------+------------+

There must be some relationship between Desk, segment and supervisor. Otherwise how are you able to assign “GLOBAL” segment to “OIL & GAS” desk and not for “AUTOMOTIVE”. Also, how do you know who is the supervisor for which desk? I am sure those assignments are not arbitrary. — Somy, Oct 02 '20 at 18:42
With the query you have written and the data I see , I would say everything is working fine and there is no problem with `union all` until you explain why it shouldn't. — Sujitmohanty30, Oct 02 '20 at 18:53
This is a summary table that I'm creating for visualization in Tableau. Basically there are additional columns like TOTAL ENTRIES which have aggregated results. In the dashboard DESK, SEGMENT and SUPERVISOR are three independent dropdowns. When a user selects DESK, the TOTAL ENTRIES will be 23. — shockwave, Oct 02 '20 at 18:58

Barbaros Özhan · Accepted Answer · 2020-10-02T19:38:36.020

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.

Soumendra Mishra · Answer 2 · 2020-10-02T19:15:18.453

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'

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

score 0 · Answer 3 · answered Oct 02 '20 at 19:19

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

SQL Merge rows instead of UNION ALL

3 Answers3

Query

Result