Skip to Main Content
  • Questions
  • How to display unique values among multiple columns

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Siddhartha.

Asked: April 09, 2022 - 6:01 am UTC

Last updated: April 25, 2022 - 2:18 pm UTC

Version: 19.8.0.0.0

Viewed 1000+ times

You Asked

Hi Tom,

I've the following table with values and want to get the unique values among the three columns.

create table x (id number, c1 varchar2(20), c2 varchar2(20), c3 varchar2(20));

insert into x values (1, 'a','a,b,c', 'c,a,e,x');

insert into x values (2, 'b,x,y,c','a,b,c,x,z', 'y,m');

insert into x values (3, 'a,b,c','a,b,c', 'a,b,c');


Expected Output :

ID Unique Values
-- --------------
1 a,b,c,e,x
2 a,b,c,m,x,y,z
3 a,b,c

Thanks


with LiveSQL Test Case:

and Connor said...

First we'll bring the columns together as one

SQL> select id, c1||','||c2||','||c3 str
  2    from x;

        ID STR
---------- -----------------------------------
         1 a,a,b,c,c,a,e,x
         2 b,x,y,c,a,b,c,x,z,y,m
         3 a,b,c,a,b,c,a,b,c


Then we'll parse the string out into a row for each elements

SQL> with t as
  2  ( select id, c1||','||c2||','||c3 str
  3    from x
  4  )
  5  select id, regexp_substr(str,'[^,]+', 1, lev) AS elem
  6  from   t, lateral (
  7    select level lev from dual
  8    connect by regexp_substr(str, '[^,]+', 1, level) is not null
  9  );

        ID ELEM
---------- ----------
         1 a
         1 a
         1 b
         1 c
         1 c
         1 a
         1 e
         1 x
         2 b
         2 x
         2 y
         2 c
         2 a
         2 b
         2 c
         2 x
         2 z
         2 y
         2 m
         3 a
         3 b
         3 c
         3 a
         3 b
         3 c
         3 a
         3 b
         3 c

28 rows selected.


Then we'll use LISTAGG DISTINCT to bring them back together without the duplicates


SQL> with t as
  2  ( select id, c1||','||c2||','||c3 str
  3    from x
  4  ),
  5  elements as (
  6    select id, regexp_substr(str,'[^,]+', 1, lev) AS elem
  7    from   t, lateral (
  8      select level lev from dual
  9      connect by regexp_substr(str, '[^,]+', 1, level) is not null
 10    )
 11  )
 12  select id, listagg(distinct elem,',') within group (order by elem) as tags
 13  from elements
 14  group by id;

        ID TAGS
---------- ------------------------------
         1 a,b,c,e,x
         2 a,b,c,m,x,y,z
         3 a,b,c

Rating

  (4 ratings)

Comments

Using XQUERY

Stew Ashton, April 11, 2022 - 9:35 am UTC

This works on each row individually with no need for a unique ID. It turns the concatenated string into a sequence, then removes duplicates from the sequence, then turns the sequence back into a string.
select * from x,
xmltable(
'
string-join(
  distinct-values(
    ora:tokenize($X, ",")
  ),
  ","
)
'
  passing C1 || ',' || C2 || ',' || C3 as X
  columns tags varchar2(4000) path '.'
);

ID C1       C2         C3       TAGS
1  a        a,b,c      c,a,e,x  a,b,c,e,x
2  b,x,y,c  a,b,c,x,z  y,m      a,b,c,m,x,y,z
3  a,b,c    a,b,c      a,b,c    a,b,c

Siddhartha, April 23, 2022 - 1:45 pm UTC

Thanks Connor, it works perfectly. I tried the solution with a bit larger dataset and got the ORA-01489 : result of string concatenation is too long. Final output should display the unique emails across all departments (will exceed 4000 bytes)

Note:
(1) Some emails are present in multiple departments.
(2) Emails are separated by colon in each department.
(3) Each department will contain huge number of emails; so the data volume will exceed 4000 bytes even for each department.

In the link below; I tried to insert more records to exceed 4000 to give you the exact scenario, but it gave PGA memory error. Hope the link will help.

https://livesql.oracle.com/apex/livesql/s/ndzstiwiy8rn7gzth3u01go48

Chris Saxon
April 25, 2022 - 2:14 pm UTC

LISTAGG is limited to the max length of a VARCHAR2. You can increase this to 32k by enabling extended data types.

There are other workarounds at:

https://livesql.oracle.com/apex/livesql/file/tutorial_EDVE861IDOIZGLUZMSW7Y8HYG.html

Siddhartha, April 23, 2022 - 1:49 pm UTC

Thanks Stew, I tried your solution with the table data I provided earlier and it worked. But when I tried with the email column ( link : https://livesql.oracle.com/apex/livesql/s/ndzstiwiy8rn7gzth3u01go48 ); i couldn't make it work.
Chris Saxon
April 25, 2022 - 2:18 pm UTC

What exactly are you trying to do with the emails?

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.