プログラマ38の日記

主にプログラムメモです。

SQL: 縦横変換で気をつけていること

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

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

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

 

[縦持ちのデータ]

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

[横持ちのデータ]

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

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

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

 

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

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


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

グループ 担当 連番
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です。OracleSQLServer、Postgresなどメジャーなデータベースはこれを使うと便利です。

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

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

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

それ以外(主にMS-ACCESS

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

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

 

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