how to group the missing attributes in single column in oracle sql

Question

from the below table I need to group the missing attributes in single column.

   empid | name  | dept     | ph_no
  ---------------------------------
  123   | null  |  null    | null
  124   | mike  |  science | null
  125   | null  |  physics | 789
  126   | null  |  null    | 463
  127   | john  |  null    | null

Expected result ...

  status
 ------------
  123,125,126 missing name
  123, 126,127 missing dept
  123,124,127 missing ph_no

https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle — juergen d, Jan 12 '18 at 10:12
I believe this is already asked, see link below https://stackoverflow.com/questions/48174903/how-to-concatenate-multiple-columns-in-oracle-sql-into-single-column-only-for-ma/48179234#48179234 — eifla001, Jan 12 '18 at 10:18

score 2 · Accepted Answer · answered Jan 12 '18 at 10:38

Unpivot the data, filter for NULL values and then aggregate:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE data ( empid, name, dept, ph_no ) AS
SELECT 123, null,   null,      null FROM DUAL UNION ALL
SELECT 124, 'mike', 'science', null FROM DUAL UNION ALL
SELECT 125, null,   'physics', 789  FROM DUAL UNION ALL
SELECT 126, null,   null,      463  FROM DUAL UNION ALL
SELECT 127, 'john', null,      null FROM DUAL;

Query 1:

SELECT LISTAGG( empid, ',' ) WITHIN GROUP ( ORDER BY empid )
         || ' missing ' || LOWER( type ) AS missing
FROM   (
  SELECT empid,
         name,
         dept,
         TO_CHAR( ph_no ) As ph_no
  FROM   data
)
UNPIVOT INCLUDE NULLS ( value FOR type IN ( name, dept, ph_no ) ) c
WHERE value IS NULL
GROUP BY type

Results:

|                   MISSING |
|---------------------------|
|  123,126,127 missing dept |
|  123,125,126 missing name |
| 123,124,127 missing ph_no |

Wow.. wow this worked very cool. Learned a new concept unpivot never heard this before in oracle. Thank you — stephenjacob, Jan 12 '18 at 10:48

score 1 · Answer 2 · answered Jan 12 '18 at 10:21

Here's one option:

SQL> with test (empid, name, dept, phno) as
  2  (select 123, null, null, null from dual union
  3   select 124, 'mike', 'science', null from dual union
  4   select 125, null, 'physics', 789 from dual union
  5   select 126, null, null, 463 from dual union
  6   select 127, 'john', null, null from dual
  7  )
  8  select
  9    listagg(case when name is null then empid end, ', ') within group (order by empid) missing_name,
 10    listagg(case when dept is null then empid end, ', ') within group (order by empid) missing_dept,
 11    listagg(case when phno is null then empid end, ', ') within group (order by empid) missing_phno
 12  from test;

MISSING_NAME         MISSING_DEPT         MISSING_PHNO
-------------------- -------------------- --------------------
123, 125, 126        123, 126, 127        123, 124, 127

SQL>

how to group the missing attributes in single column in oracle sql

2 Answers2