Oracle db. How to get a result set of unique values of pretty much arbitrary amount of columns of the same table

Having this column structure

Id, col_1, col_2, col_3
1    A      A      B
2    B      B      B
3    C      C      D

Is it possible to get one-column output with unique values?

res_column
 A
 B
 C
 D

What I’ve already tried:

1. Union seems doable only in case there are 2 to 3 columns, which is not the case.
2. I’ve found a pivot/unpivot, but I didn’t really grasp it.

Answer

with
  sample_inputs (id, col_1, col_2, col_3) as (
    select 1, 'A', 'A', 'B' from dual union all
    select 2, 'B', 'B', 'B' from dual union all
    select 3, 'C', 'C', 'D' from dual
  )
select  distinct res_column
from    sample_inputs
unpivot (res_column for col in (col_1, col_2, col_3))
order   by res_column    ---   if needed
;

RES_COLUMN
----------
A
B
C
D

If you must handle null also, you can do so with the include nulls option to unpivot. Without this option, the query above will still work – it will just ignore (discard, exclude) all null from the unpivoted columns.

with
  sample_inputs (id, col_1, col_2, col_3) as (
    select 1, 'A' , 'A' , 'B'  from dual union all
    select 2, 'B' , 'B' , 'B'  from dual union all
    select 3, 'C' , 'C' , 'D'  from dual union all
    select 4, null, null, null from dual
  )
select  distinct res_column
from    sample_inputs
unpivot include nulls (res_column for col in (col_1, col_2, col_3))
order   by res_column    ---   if needed
;


RES_COLUMN
----------
A
B
C
D
 
 
(five rows selected)

Note that the last result has five rows, not four; the last value is null, which is invisible, but if you run the query in an editor that shows you the number of rows (or if you use an option to show null as <null> or such), then you will see it.

NOTE: You can’t make this “generic” in plain SQL; the number of columns to unpivot, and their names, must be hard-coded in the query. If you need a “generic” solution you will need to use dynamic SQL; that is a much more advanced topic, and certainly not appropriate if you don’t even grasp static unpivot yet.