Coalesce with no preference to order

Question

I have 3 columns I'm trying to move into 1. In each row, exactly only 1 of the 3 will be populated. For example:

**col1 | col2 | col3**  
 10    | null | null  
 null  | 15   | null  
 null  | null | 9  
 22    | null | null  
 null  | 2    | null  
 17    | null | null  

I want to now coalesce all the columns into one.
First off, is that the best way to do it?

Secondly, is there a way to specify to COALESCE() whether or not the order of arguments matters?

edit: final result should be:

col1  
----  
10  
15  
9  
22  
2  
17

Answer 1

Try this:

SELECT (SELECT MAX(v) 
        FROM (VALUES (col1), (col2), (col3)) AS x(v))
FROM mytable        

The above query uses Table Value Constructor in order to build an inline table with all three table columns. Applying MAX on this table returns the non-null value.

Demo here

Edit:

It seems you can get at the required result with a simple use of COALESCE:

SELECT COALESCE(col1, col2, col3) AS col
FROM mytable 

The COALESCE expression will return the value of the first not-null field.