PL/SQL finding and let row wise duplicates

Question

I have a table with 10 columns. Looks like this

NULL 1 1 NULL NULL NULL NULL NULL 2 NULL
1 NULL NULL 2 NULL 1 NULL NULL 2 NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL 2 NULL

I need a SQL Query that returns this: (distinct row-wise)

1 2
1 2
NULL
2

I dont understand whats the logic of above output some rows include nulls some not and that is not row vise distinct but column vise — Himanshu, Apr 15 '20 at 14:25
@HimanshuAhuja I want per row all distinct values except the NULL — Jakob Hahn, Apr 15 '20 at 14:28

score 2 · Answer 1 · answered Apr 15 '20 at 14:33

You can use the hiearchy query as follows:

SELECT LISTAGG(E, ' ') WITHIN GROUP( ORDER BY E )
FROM
    ( SELECT DISTINCT
            ROWID AS RID,
            DECODE(COLUMN_VALUE, 1, COL1, 2, COL2, 3, COL3, 4, COL4 ..., 10, COL10) AS E
        FROM YOUR_TABLE
             CROSS JOIN TABLE ( CAST(MULTISET(
                SELECT LEVEL
                  FROM DUAL
                CONNECT BY LEVEL <= 10 ) AS SYS.ODCINUMBERLIST) )
    )
GROUP BY RID;

Himanshu · Answer 2 · 2020-04-15T15:27:37.287

1

Try below if that helps trying to unpivot the columns into 1 eliminating nulls keeping the rowid mantained for each previous selection before unpivoting then aggregating via grouping

    Select op from (Select rn, 
     listagg(distinct col, 
       ',') within group (order by 
         col) as  op from
   (  Select *, row_number() over(
    Partition by col1, col2.. Col10 order by
   ..) rn
     from table
    UnPIVOT 
  (COL, num) IN   ( (COL1, COL2,... 
     Col10), rn)) 

   ) where col is not null group by 
  rn)

edited Apr 15 '20 at 15:27

answered Apr 15 '20 at 14:58

Himanshu

3,830
2
10
29

`LISTAGG` does not support the `DISTINCT` keyword. – MT0 Apr 15 '20 at 15:09
https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values check this for newer version – Himanshu Apr 15 '20 at 15:29
The OP is using Oracle 18c - `LISTAGG( DISTINCT ... )` is not supported. – MT0 Apr 15 '20 at 15:30

score 1 · Accepted Answer · answered Apr 15 '20 at 15:12

You can UNPIVOT the columns to rows (and keep the NULL values) and then filter to keep DISTINCT rows and then aggregate the rows into a string ignoring the NULL values unless they're all NULL:

SELECT LISTAGG( value, ' ' ) WITHIN GROUP ( ORDER BY value ) AS vals
FROM   (
  SELECT DISTINCT
         rn,
         value,
         COUNT(value) OVER ( PARTITION BY rn ) AS num_values
  FROM   (
    SELECT ROWNUM AS rn,
           t.*
    FROM   table_name t
  )
  UNPIVOT INCLUDE NULLS (
    value FOR key IN ( col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 )
  )
)
WHERE num_values = 0
OR    value IS NOT NULL
GROUP BY rn

So, for your sample data:

CREATE TABLE table_name (
  col1 NUMBER,
  col2 NUMBER,
  col3 NUMBER,
  col4 NUMBER,
  col5 NUMBER,
  col6 NUMBER,
  col7 NUMBER,
  col8 NUMBER,
  col9 NUMBER,
  col10 NUMBER
);

INSERT INTO table_name
SELECT NULL,    1,    1, NULL, NULL, NULL, NULL, NULL,    2, NULL FROM DUAL UNION ALL
SELECT    1, NULL, NULL,    2, NULL,    1, NULL, NULL,    2, NULL FROM DUAL UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,    2, NULL FROM DUAL;

This outputs:

| VALS |
| :--- |
| 1 2  |
| 1 2  |
| null |
| 2    |

db<>fiddle here

@HimanshuAhuja You are trying to use `LISTAGG( DISTINCT ... )` which is syntactically invalid on Oracle 18c; I have a sub-query to perform the `DISTINCT` filtering before using `LISTAGG` and also have additional filtering to keep the `NULL` value when all the columns are `NULL`. — MT0, Apr 15 '20 at 15:31

PL/SQL finding and let row wise duplicates

3 Answers3