7-81 nullの列を左に詰めて表示

SQLパズル

NullToLeftテーブル
Col1  Col2  Col3  Col4
----  ----  ----  ----
   1     2     3     4
   1     2     3  null
   1     2  null     4
   1  null  null     4
null     2     3     4
null  null  null  null

nullの列を左に詰めて表示する。

出力結果
New1  New2  New3  New4
----  ----  ----  ----
   1     2     3     4
   1     2     3  null
   1     2     4  null
   1     4  null  null
   2     3     4  null
null  null  null  null

データ作成スクリプト

create table NullToLeft(Col1,Col2,Col3,Col4) as
select    1,   2,   3,   4 from dual union all
select    1,   2,   3,null from dual union all
select    1,   2,null,   4 from dual union all
select    1,null,null,   4 from dual union all
select null,   2,   3,   4 from dual union all
select null,null,null,null from dual;

SQL

--■■■case式の場合分け機能と短絡評価機能を使う方法■■■
select
case when Sum1 = 1 then Col1
     when Sum1+Sum2 = 1 then Col2
     when Sum1+Sum2+Sum3 = 1 then Col3
     when Sum1+Sum2+Sum3+Sum4 = 1 then Col4 end as new1,
case when Sum1+Sum2 = 2 then Col2
     when Sum1+Sum2+Sum3 = 2 then Col3
     when Sum1+Sum2+Sum3+Sum4 = 2 then Col4 end as new2,
case when Sum1+Sum2+Sum3 = 3 then Col3
     when Sum1+Sum2+Sum3+Sum4 = 3 then Col4 end as new3,
case when Sum1+Sum2+Sum3+Sum4 = 4 then Col4 end as new4
from (select Col1,Col2,Col3,Col4,
      nvl2(Col1,1,0) as Sum1,
      nvl2(Col2,1,0) as Sum2,
      nvl2(Col3,1,0) as Sum3,
      nvl2(Col4,1,0) as Sum4
      from NullToLeft);

--■■■UnPivotとnth_value関数を使う方法■■■
select distinct
nth_value(Vals,1) ignore nulls over(partition by rn
order by SK Rows between Unbounded Preceding and Unbounded Following) as New1,
nth_value(Vals,2) ignore nulls over(partition by rn
order by SK Rows between Unbounded Preceding and Unbounded Following) as New2,
nth_value(Vals,3) ignore nulls over(partition by rn
order by SK Rows between Unbounded Preceding and Unbounded Following) as New3,
nth_value(Vals,4) ignore nulls over(partition by rn
order by SK Rows between Unbounded Preceding and Unbounded Following) as New4
from (select Col1,Col2,Col3,Col4,RowNum as rn
      from NullToLeft)
UnPivot(Vals for SK in(Col1 as 1,
                       Col2 as 2,
                       Col3 as 3,
                       Col4 as 4))
order by New1,New2,New3,New4;

解説

case式の場合分け機能と短絡評価機能を使うのがオススメですね。

US-OTN --- How to remove gaps/null values from set of columns in a row

---