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

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

declare @t as table(
  [id] int,
  [col_1] VARCHAR(100),
  [col_2] VARCHAR(100),
  [col_3] VARCHAR(100)
);

insert into @t values
(1, 'a', 'b', null),
(2, null, 'b', 'c'),
(3, 'd', 'e', null),
(4, null, 'e', 'f');  

;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 @t
        where [col_1] is not null
        union
        select [col_2], 'col_2'
        from @t
        where [col_2] is not null
        union
        select [col_3], 'col_3'
        from @t
        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];

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results