SQL: Alternative to COALESCE() function that groups all values into one column

Question

I have a table like this

Using the COALESCE() function...

SELECT COALESCE(column1, column2, column3) combinedColumn from t;

I get this...

However, I want this....

I found a work around using UNION ALL but this isn't very elegant. Is there a function that works like COALESCE() except includes all values? Thanks

Order is not important in this case. I just want to combine all values into one column — cget, May 14 '19 at 13:04
There is a very good example in the next link: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows ; try to adapt to work on your case. — F.Lazarescu, May 14 '19 at 13:18

score 2 · Answer 1 · answered May 14 '19 at 13:18

You can't use a coalesce here as there might be more than one value to return

UNION is the best solution (not UNION ALL because blanks)

select column1 from mytable
UNION
select column2 from mytable
UNION
select column3 from mytable

That said, if you want to maintain duplicates (if any), it's UNION ALL, or joining all the columns into a single string then splitting them out again (avoiding the UNION ALL at any cost)

SQL: Alternative to COALESCE() function that groups all values into one column

1 Answers1