SQL: 縦横変換で気をつけていること - プログラマ３８の日記

SQLで縦持ちから横持ちへ変換、横持ちから縦持ちの変換を行うことは多いと思います。

横持ちから縦持ちへの変換は、union all を使うことで実現ができ、union allはどのデータベースでも使えるので困ることは特にありません。

ですが、縦持ちから横持ちへの変換はデータベースでやり方を変える必要があり気をつけています。

[縦持ちのデータ]

グループ	担当
Aグループ	佐藤
Aグループ	山田
Aグループ	田中
Aグループ	鈴木
Bグループ	池田
Bグループ	田中
Bグループ	鈴木

[横持ちのデータ]

グループ	担当１	担当２	担当３	担当４	担当５
Aグループ	佐藤	山田	田中	鈴木
Bグループ	池田	田中	鈴木

上記のデータで縦持ちから横持ちへの変換を考えます。

上記の縦持ちのデータの担当者は実はあいうえお順で並んでいます。そして、縦持ちの時の並びでそのまま担当1、担当2…と横持ちに変換されています。

上の縦持ちから横持ちへの変換は次のようになります。

縦持ちのデータに、グループ毎に担当者順で1から連番を振る。
１で振られた連番を場合分けの条件に使い担当1～5の項目を用意した後、グループで集約する。

１の後のデータは次の通りです。

グループ	担当	連番
Aグループ	山田	1
Aグループ	田中	2
Aグループ	鈴木	3
Aグループ	佐藤	4
Bグループ	田中	1
Bグループ	鈴木	2
Bグループ	池田	3

そして2のSQLは次の通りとなります。※caseの部分は、データベース毎の関数に置き換えて使う事になります。(MS-ACCESSだとIIFを使います)

Select
   グループ
  ,max( case when 連番=1 then 担当 end )
  ,max( case when 連番=2 then 担当 end )
  ,max( case when 連番=3 then 担当 end )
  ,max( case when 連番=4 then 担当 end )
  ,max( case when 連番=5 then 担当 end )
From 1のデータ
Group By グループ

そして、1の連番を振るところが、データベース毎に工夫が必要になります。

Window関数が使える場合

Select
   グループ
  ,担当
  ,row_number() over(partition by グループ order by 担当 asc)
From 縦持ちのデータ

連番を振るWindow関数はrow_numberです。OracleやSQLServer、Postgresなどメジャーなデータベースはこれを使うと便利です。

スカラ副問合せが使える場合

Select
   グループ
  ,担当
  ,(select count(*) from 縦持ちのデータ スカラ 
    where データ.グループ = スカラ.グループ
      and データ.担当 >= スカラ.担当) as 連番
From
  縦持ちのデータ データ

この書き方で同じような結果がでます。気をつけたいのが同じグループ内に担当が重複していると正しい連番になりません。事前に重複を排除するなどが必要です。

それ以外（主にMS-ACCESS）

Select
   データ.グループ
  ,データ.担当
  ,count(*)
From
  縦持ちのデータ データ
inner join 
  縦持ちのデータ 連番用
on  データ.グループ = 連番用.グループ
and データ.担当 >= 連番用.担当
group by
  データ.グループ
 ,データ.担当

SQLパズルのようになりますが、MS-ACCESSではこうやらざると得ないと思っています。こちらも同じグループ内で担当が重複してると正しい連番になりません。事前に重複を排除するなどが必要です。

縦横変換も、月ごとに横に並べる場合などは、最初から月を場合分けの条件にすればいいのですが、場合分けの条件にする項目が用意されていない場合は、連番を振る必要があります。その時には上記のいずれかを使うことになると思います。