Clifton Miranda Clifton Miranda - 1 month ago 6
SQL Question

I want to select all distinct for every column from sql table

country state city
Country1 state1 city5
Country2 state2 city6
Country5 state1 city6
Country5 state3 city6
Country2 state3 city9


i want to get distinct values from country, state, city and all these columns should not be blank. i tried couple of queries but does not work ..The output should be all distinct values as shown below

Country1
Country2
Country5
state1
state2
state3
city5
city6
city9

Answer

Simple way is using UNION

Select country from yourtable
Union
Select state from yourtable
Union 
Select city from yourtable

This can be done at ease if your DBMS supports APPLY operator

SELECT datas
FROM   Youtable s
       CROSS apply (SELECT [country] UNION
                    SELECT [state] UNION
                    SELECT [city]) cs (datas) 

If your DBMS supports LATERAL then (thanks to a_horse_with_no_name)

select t.x
from the_table
  cross join lateral (select country union select state union select city ) as t(x)
order by t.x