## Question and Answer

## You Asked

Hi Tom,

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

ID Unique Values

-- --------------

1 a,b,c,e,x

2 a,b,c,m,x,y,z

3 a,b,c

Thanks

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

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

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

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

# Comments

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

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

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

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

There are other workarounds at:

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

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.

What exactly are you trying to do with the emails?