SQL – First n non null columns

I have 30 columns like p1, p2, p3,……,p29, p30. Out of them, any 6 consecutive values will be non-null and the rest are all none.

I need to write an SQL query (preferably Redshift) to get all of them into 6 columns. Say a1,a2,a3,a4,a5,a6

Eg. If I have 50 rows of data with 30 columns with a lot of nulls. I’ll be turning it into 50 rows of data with those 6 non-null values of a row.

Answer

There is no simple way to do this. One method is to unpivot and then re-aggregate — assuming your table has a primary key:

select pk,
       max(case when seqnum = 1 then p end) as q1,
       max(case when seqnum = 2 then p end) as q2,
       max(case when seqnum = 3 then p end) as q3,
       max(case when seqnum = 4 then p end) as q4,
       max(case when seqnum = 5 then p end) as q5,
       max(case when seqnum = 6 then p end) as q6
from (select pk, p, row_number() over (partition by pk order by which) as seqnum
      from ((select pk, 1 as which, p1 as p from t) union all
            (select pk, 2 as which, p2 as p from t) union all
            . . . 
           ) t
      where p is not null
     )  t
group by pk