0

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
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
Mukul Kumar
  • 564
  • 1
  • 5
  • 24
  • first unpivot https://stackoverflow.com/questions/15184381/mysql-how-to-unpivot-columns-to-rows then distinct – P.Salmon Jul 18 '22 at 06:52

2 Answers2

1

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
Radagast
  • 5,102
  • 3
  • 12
  • 27
0

UNION should achieve this but can be computationally expensive depending on how many you are performing and the amount of data it needs to de-dupe

Something like:

SELECT adb1 FROM xyz
UNION
SELECT adb2 FROM xyz
UNION
SELECT adb3 FROM xyz
Dean Flinter
  • 654
  • 3
  • 6