Finding missing values in aggregated list Oracle SQL

user_9692406 0
Viewed 34

I have a table similar to this

ID Value
A AA
A AC
B AB
B AD

This table is grouped by ID and I get a list of values

SELECT ID, LISTAGG(value, ', ') WITHIN GROUP (ORDER BY value)
  FROM table 
 GROUP BY ID

All possible values are AA, AB, AC, AD. I would like to show the missing values. So the results should be

ID Value
A AB, AD
B AA, AC

Is there any elegant way to solve this? I am working on oracle 19

oracle sql
1 Answers

You can find all the DISTINCT values and then use a PARTITIONed OUTER JOIN to find the missing ones:

SELECT t.id,
       LISTAGG(v.value, ', ') WITHIN GROUP (ORDER BY v.value) AS missing
FROM   (SELECT DISTINCT VALUE FROM table_name) v
       LEFT OUTER JOIN table_name t
       PARTITION BY (t.id)
       ON (v.value = t.value)
WHERE  t.value IS NULL
GROUP BY t.id;

Which, for the sample data:

CREATE TABLE table_name (ID, VALUE) AS
SELECT 'A', 'AA' FROM DUAL UNION ALL
SELECT 'A', 'AC' FROM DUAL UNION ALL
SELECT 'B', 'AB' FROM DUAL UNION ALL
SELECT 'B', 'AD' FROM DUAL;

Outputs:

ID MISSING
A AB, AD
B AA, AC

db<>fiddle here

user_15092640
Related