Combining Multiple Columns into One based off another field

Question

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.

Answer 1

You can merge columns into an array (array[col1, col2, ...]), filter out nulls and then unnest:

-- sample data
WITH dataset (MyID, col1, col2) AS (
    VALUES  
        (1, null, 'RED'),
        (2, 'GREEN', 'RED')
) 

--query
select myid,
    c as CombinedColumn
from dataset
    cross join unnest (filter(array[col1, col2], s->s is not null)) as t(c) t(c)

Output:

myid	CombinedColumn
1	RED
2	GREEN
2	RED