CODEWITHSUNDEEP
sqlamazon-redshift
Mike
Mike

Reputation: 2434

Getting the Nth non-null column in redshift

I have a table with a collection of nullable columns. What I need to do is 'shift' these columns "left" so that column 1 contains the first non-null value, column 2 contains the second non-null value, etc.

e.g.

This:

<null>, <null>, foo,    bar,    baz,    buz
<null>, <null>, <null>, <null>, foo,    bar
<null>, <null>, <null>, foo,    bar,    baz

needs to become this: 

foo,    bar,    baz,    buz,    <null>, <null>
foo,    bar,    <null>, <null>, <null>, <null>
foo,    bar,    baz,    <null>, <null>, <null>

I can be certain that the data contains 0 or more columns followed by 1 or more non-null columns.

I can imagine a way to acheive this with gratuitous use CASE statements, but I'd rather not create such an abomination.

Anyone have a clue how to get this done easily?

Upvotes: 0

Views: 465

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think the simplest way is an unpivot/pivot. You can do this as:

select id,
       max(case when seqnum = 1 then col end) as col1,
       max(case when seqnum = 2 then col end) as col2,
       max(case when seqnum = 3 then col end) as col3,
       max(case when seqnum = 4 then col end) as col4,
       max(case when seqnum = 5 then col end) as col5,
       max(case when seqnum = 6 then col end) as col6
from (select t.*, row_number() over (partition by id order by pos) as seqnum
      from ((select id, col1 as col, 1 as pos from t) union all
            (select id, col2 as col, 2 as pos from t) union all
            (select id, col3 as col, 3 as pos from t) union all
            (select id, col4 as col, 4 as pos from t) union all
            (select id, col5 as col, 5 as pos from t) union all
            (select id, col6 as col, 6 as pos from t) 
           ) t
      where col is not null
     ) t
group by id;

Because Redshift is a columnar database, the union all should be about as efficient as any other way of unpivoting the data.

Upvotes: 1

Related Questions