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

Question

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

Gordon Linoff · Accepted Answer · 2013-07-23 10:47:05Z

4

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.)

answered Jul 23, 2013 at 10:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robert · Accepted Answer · 2013-07-23 10:44:22Z

3

Try to use union with both columns

select Department1 
from tab
union 
select Department2 
from tab

NOTE: union command eliminates duplicates

answered Jul 23, 2013 at 10:44

Robert

25.8k8 gold badges70 silver badges86 bronze badges

2 Comments

ElChupanibre Over a year ago

Thank you, that was exactly what I was looking for - and learned that union eliminates duplicates.

Robert Over a year ago

@ElChupanibre Don't problem, but remember ;)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related