10-181 符号に応じた縦横変換

SQLパズル

SignTable
Col1
----
 -50
 -40
 -30
   0
   0
  10
  20
  30
  30
  40

各値の符号に応じて、
以下の出力を行う。

出力結果
Positive  Zero  Negative
--------  ----  --------
      10     0       -50
      20     0       -40
      30  null       -30
      30  null      null
      40  null      null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table SignTable as
select -50 as Col1 from dual
union all select -40 from dual
union all select -30 from dual
union all select   0 from dual
union all select   0 from dual
union all select  10 from dual
union all select  20 from dual
union all select  30 from dual
union all select  30 from dual
union all select  40 from dual;

SQL

select
max(case when Col1 > 0 then Col1 end) as Positive,
max(case when Col1 = 0 then Col1 end) as Zero,
max(case when Col1 < 0 then Col1 end) as Negative
from (select Col1,
      Row_Number() over(partition by sign(Col1) order by Col1) as Rank
        from SignTable)
group by Rank
order by Rank;

解説

sign関数の値で、パーティションを切ってます。

---