CODEWITHSUNDEEP
sqloracle-databasedistinct
amstergc20
amstergc20

Reputation: 117

Oracle SQL for Distinct Values in Multiple Columns

I have 10 columns with names for different task numbers. I want a list of all of the names distinctly.

I have tried this and am getting Oracle Error.

select distinct(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10) from x where y is not null;

a1 - a10 are all names and I just want the aggregated list of distinct values.

Upvotes: 0

Views: 890

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

SELECT distinct A 
FROM X
UNPIVOT(
    A  -- unpivot_clause
    FOR A_N --  unpivot_for_clause
    IN ( -- unpivot_in_clause
        a1,a2,a3,a4,a5,a6,a7,a8,a9,a10
    )
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method is a lateral join:

select distinct a
from t cross join lateral
     (select t.a1 as a from dual union all
      select t.a2 from dual union all
      select t.a3 from dual union all
      select t.a4 from dual union all
      select t.a5 from dual union all
      select t.a6 from dual union all
      select t.a7 from dual union all
      select t.a8 from dual union all
      select t.a9 from dual union all
      select t.a10 from dual
     ) s
where a is not null;

In earlier versions, you can use union all:

select a1 from t where a1 is not null
union  -- on purpose to remove duplicates
select a2 from t where a2 is not null
union  -- on purpose to remove duplicates
. . .

Upvotes: 1

Related Questions