CODEWITHSUNDEEP
sqlsql-server
Macca424
Macca424

Reputation: 65

SQL select multiple columns to return one distinct field

I am looking to return a (single) column that has the distinct values of 4 columns that I will be looking up within the same table.

I've tried 

"select distinct e1l,e2l,e1s,e2s from jobmovement"

but this is just returning each distinct occurrence of the four tables, so for example if there were 4 lines of 178,178,178,178 it would just return 1 of these.

So for example, I have 4 column headers (E1L,E2L,E1S,E2S):

E1L,E2L,E1S,E2S

178,178,178,178 , ,216,216 ,178, , 217,217,178,216

I would this to return in the single column the distinct values

178
216
217

Any help would be appreciated. Thanks, Paul.

Upvotes: 1

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

Use apply to unpivot the data and then use select distinct:

select distinct v.e
from jobmovement jm cross apply
     (values (jm.e1l), (jm.e2l), (jm.e1s), (jm.e2s)) v(e);

Upvotes: 1

Related Questions