Return column name with coalesce

Question

I want to select a non-empty value from multiple columns in my table first. I usually used

SELECT COALESCE(col1, col2, col3) FROM table 

but this time I don't need a value, but the name of the column (or specific text for each column) with the first non-zero value.

Example:

Table:

 col1 | col2 | col3 null | null | 3 null | 5 | 8 2 | null | 2 

should return:

 col3 col2 col1 

Is there a way to do this with a single SQL statement?

Note. I am using PostgreSQL.

Answer 1

I would suggest using the CASE statement

 SELECT CASE WHEN col1 IS NOT NULL THEN 'col1' WHEN col2 IS NOT NULL THEN 'col2' WHEN col3 IS NOT NULL THEN 'col3' ELSE NULL END FROM table; 

Answer 2

You can also use coalesce with replacement if the entire column is a row.

 SELECT REPLACE(COALESCE(COL1||'COL1',COL2||'COL2',COL3||'COL3'),COALESCE(COL1,COL2,COL3),'') FROM YOUR_TABLE; 

If the entire column is number, you can:

 SELECT 'COL'||CAST(COALESCE(COL1+1,COL2+2,COL3+3) - COALESCE(COL1,COL2,COL3) AS CHAR(1)) FROM YOUR_TABLE;