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
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,cThen 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