SQL: How to make a Coalesce on each VARCHAR column after a group by?

Question

Using the table below:

id: integer| col_1: VARCHAR| col_2: VARCHAR | col_3: VARCHAR
------------------------------------------------------------
      1    |       'a'     |       'b'      |       null 
      2    |       null    |       'b'      |       'c'
      3    |       'd'     |       'e'      |       null
      4    |       null    |       'e'      |       'f'     

I would like to get the following result:

'a' | 'b' | 'c'
'd' | 'e' | 'f'

I tried this query:

SELECT colaesce(t.col_1), colaesce(t.col_2), coalesce(t.col_3)
FROM ( select * from table ) t 
INNER JOIN table ON t.col_2 = table.col_2;

I'm new to SQL and I would appreciate any help !

Answer 1

Try something like this with UNION.

Query

;with cte as(
    select [rn] = row_number() over(
        partition by t.[cols]
        order by t.[col_val]
    ), *
    from(
        select [col_1] [col_val], 'col_1' [cols]
        from [your_table_name]
        where [col_1] is not null
        union
        select [col_2], 'col_2'
        from [your_table_name]
        where [col_2] is not null
        union
        select [col_3], 'col_3'
        from [your_table_name]
        where [col_3] is not null
    )t
)
select 
max(case [cols] when 'col_1' then [col_val] end) [col_1],
max(case [cols] when 'col_2' then [col_val] end) [col_2],
max(case [cols] when 'col_3' then [col_val] end) [col_3]
from cte
group by [rn];

Find demo here