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 |
CodePudding user response：
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
CodePudding user response：
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