CODEWITHSUNDEEP
sql
ElChupanibre
ElChupanibre

Reputation: 13

SQL select single distinct values from multiple columns (not combined values)

For example, I have the following table:

UserId Department1 Department2
------------------------------
1      Sales       Marketing
2      Research
3      Sales

And I want to get the following Results

DistinctDepartments
-------------------
Sales
Marketing
Research

I found lots of solutions that show me the distinct combined values of multiple columns, but I need the overall distinct values, as if all values would be in the same column.

Thankyou

Chris

Upvotes: 1

Views: 1160

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Using union is definitely a reasonable approach to this problem. In most databases, though, the following may be faster:

select distinct (case when n = 1 then Department1 else Department2 end)
from tab cross join
     (select 1 as n union all select 2) n

The reason is that the table is only scanned once, rather than once for each column. This can be particularly important when tab is not really a table, but a more expensive view. (And some databases support unpivot which behaves similarly to this.)

Upvotes: 4

Robert
Robert

Reputation: 25753

Try to use union with both columns

select Department1 
from tab
union 
select Department2 
from tab

NOTE: union command eliminates duplicates

Upvotes: 3

Related Questions