Combining Multiple Columns into One based off another field

byMR

January 26, 2022

I am using AWS Athena and want to combine multiple columns into one new field.

Example: The above table shows what my data set looks like.

And the output I desire is:

So it combines to one columns only if there is a value within it, and for the ID.

>Solution :

Use this :

Select MyId,Col1 as CombininedCol from YourTable where Col1 is not null
Union All
Select MyId,Col2 as CombininedCol from YourTable where Col2 is not null
Union All
Select MyId,Col3 as CombininedCol from YourTable where Col3 is not null
Union All
Select MyId,Col4 as CombininedCol from YourTable where Col4 is not null
order by MyId

Tip: The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).

Each select separately get the two columns of MyID and the Cole 1,2, etc., and finally returns them as two columns.