Selecting Distinct Values from Multiple Columns in a Table and Returning the Result in One Column

Question

I have a table in my database with multiple columns, and I want to select distinct values from these columns and concatenate the results into a single string. How can I achieve this in a single SQL query?

ID  COL1    COL2    COL3
1   P1      d1      V1
2   P1      d2      V1
3   P3      d1      V1
4   P3      d2      V1
5   P4      d1      V1
6   P4      d2      V1

Expected result :

P1;P3;P4;V1;d1;d2

Answer 1

In Oracle 11g, you'll need to take several steps:

SQL> select * From v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> select * From test;

        ID CO CO CO
---------- -- -- --
         1 P1 d1 V1
         2 P1 d2 V1
         3 P3 d1 V1
         4 P3 d2 V1
         5 P4 d1 V1
         6 P4 d2 V1

6 rows selected.

SQL>
SQL> with
  2    c1 as (select distinct col1 from test),
  3    c2 as (select distinct col2 from test),
  4    c3 as (select distinct col3 from test),
  5  --
  6    c1l as (select listagg(col1, ';') within group (order by col1) cl1 from c1),
  7    c2l as (select listagg(col2, ';') within group (order by col2) cl2 from c2),
  8    c3l as (select listagg(col3, ';') within group (order by col3) cl3 from c3)
  9  --
 10  select cl1 ||';'|| cl2 ||';'|| cl3 as result
 11  from c1l cross join c2l cross join c3l;

RESULT
--------------------------------------------------------------------------------
P1;P3;P4;d1;d2;V1

SQL>

---

In higher versions - which support distinct within listagg - things get simpler:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL> select
  2    listagg(distinct col1, ';') within group (order by col1) ||';'||
  3    listagg(distinct col2, ';') within group (order by col2) ||';'||
  4    listagg(distinct col3, ';') within group (order by col3) result
  5  from test;

RESULT
--------------------------------------------------------------------------------
P1;P3;P4;d1;d2;V1

or even

SQL> select
  2    listagg(distinct col1, ';') ||';'||
  3    listagg(distinct col2, ';') ||';'||
  4    listagg(distinct col3, ';') result
  5  from test;

RESULT
--------------------------------------------------------------------------------
P1;P3;P4;d1;d2;V1

SQL>

Answer 2

You can use UNION to merge all your columns into one column then LISTAGG to concatenate the result into a single string :

select listagg(col, ';') within group (order by col) as output
from (
  select col1 as col from mytable
  union
  select col2 as col from mytable
  union
  select col3 as col from mytable
);

Results :

OUTPUT
P1;P3;P4;V1;d1;d2

Demo here

Answer 3

You can do it in a single table scan (i.e. without SELECTing from the table multiple times and needing to use UNION or self-joins to re-combine those multiple queries).

UNPIVOT the columns to rows then find the DISTINCT values and finally use LISTAGG to aggregate them all into a single string:

SELECT LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS value
FROM   (
  SELECT DISTINCT value
  FROM   table_name
         UNPIVOT (value FOR col IN (col1, col2, col3))
)

In later versions that support LISTAGG(DISTINCT ...) you can use:

SELECT LISTAGG(DISTINCT value, ',') WITHIN GROUP (ORDER BY value) AS value
FROM   table_name
       UNPIVOT (value FOR col IN (col1, col2, col3))

Which, for the sample data:

CREATE TABLE table_name (ID, Col1, Col2, Col3 ) AS
  SELECT 1, 'P1', 'd1', 'V1' FROM DUAL UNION ALL
  SELECT 2, 'P1', 'd2', 'V1' FROM DUAL UNION ALL
  SELECT 3, 'P3', 'd1', 'V1' FROM DUAL UNION ALL
  SELECT 4, 'P3', 'd2', 'V1' FROM DUAL UNION ALL
  SELECT 5, 'P4', 'd1', 'V1' FROM DUAL UNION ALL
  SELECT 6, 'P4', 'd2', 'V1' FROM DUAL

Outputs:

VALUE
P1,P3,P4,V1,d1,d2

Oracle 11g Fiddle Oracle 21 Fiddle