Search code examples
mysqlsnowflake-cloud-data-platformdistinct

distinct of multiple columns snowflake

I have a table called XYZ having 3 columns adb1, adb2, adb3.(All columns are of number type)

I want a query to return a distinct number from all these 3 columns as a single row.

For example:- Table XYZ

adb1 adb2 adb3
11 12 13
12 24 25
78 25 13

Now the query should return one single column having distinct values from all these columns i.e.

Result column
11
12
13
24
25
78
Solution

  • An alternative using flatten. The idea is to create an array using your columns and then flatten them up in multiple rows

    select distinct t2.value::integer as new_col --cast it to appropriate data type
from your_table t1, 
     lateral flatten(input=>[t1.adb1,t1.adb2,t1.adb3]) t2