SQL：如何在组之后的每个VARCHAR列上创建一个Coalesce？

Question

参考下表：

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'  

我希望得到以下结果：

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

我尝试此查询：

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; 

我是新来的SQL和我将不胜感激任何帮助！

Answer 1

用UNION尝试这样的东西。

查询

;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