【備忘】縦横変換・横縦変換やってみた - SQL・Excel上にて

目次
• Introduction
• 縦横変換
• SQL
• Excel
• 横縦変換
• SQL
• Excel
• 終わりに
• おまけ
• 参考文献

Introduction

仕事柄、DB上 縦持ちリスト形式で持っているデータを、横持ちのマトリクス表形式にしたい、またはその逆をやりたいという場面や顧客要件にちょくちょく出くわす。その度に、どうやってやるんだっけ？と頭をグリグリしたり、Google先生にきいたりしている学習能力のない状態を脱却すべく、自分用の覚書を書いてみた。ちなみにテストデータは自前家計簿テスト環境から。

今回、SQLとExcelでそれぞれ試してみて気づいた点として、SQLとExcelでは得意・簡単にできる変換方向が逆という面白い状態になっていました。それぞれ得意・不得意があるわけですね。

縦横変換

列に会計期間・勘定科目グループ・金額を持ち、勘定科目グループ毎の金額を縦（行）方向に持つデータから、会計期間をキーに勘定科目グループを列方向に持つマトリクス表を作ります。

SQL

select
  period,
  max(CASE WHEN acgrp_cd = '002000' THEN sumval END) as "002000",
  max(CASE WHEN acgrp_cd = '003000' THEN sumval END) as "003000",
  max(CASE WHEN acgrp_cd = '004000' THEN sumval END) as "004000",
  max(CASE WHEN acgrp_cd = '005000' THEN sumval END) as "005000",
  max(CASE WHEN acgrp_cd = '006000' THEN sumval END) as "006000",
  max(CASE WHEN acgrp_cd = '007000' THEN sumval END) as "007000",
  max(CASE WHEN acgrp_cd = '009000' THEN sumval END) as "009000",
  max(CASE WHEN acgrp_cd = '010000' THEN sumval END) as "010000",
  max(CASE WHEN acgrp_cd = '099000' THEN sumval END) as "099000",
  max(CASE WHEN acgrp_cd = 'B00000' THEN sumval END) as "B00000",
  max(CASE WHEN acgrp_cd = 'C00000' THEN sumval END) as "C00000",
  max(CASE WHEN acgrp_cd = 'P00000' THEN sumval END) as "P00000",
  max(CASE WHEN acgrp_cd = 'P90000' THEN sumval END) as "P90000",
  max(CASE WHEN acgrp_cd = 'Z00000' THEN sumval END) as "Z00000"
from tbl
group by period;

CASE分岐で勘定科目グループ毎のレコードを列方向に持ってくるやり方。考え方を理解するのに少し時間がかかり、また記述もめんどくさいね。

Excel

ピポットテーブルを使うと一瞬でできてしまいます。本当にExcelのピポットテーブルは優秀。

ちなみにピポットテーブルを使用せず、数式だけで実現しようとするとこんな感じ。列方向に勘定科目グループを並べ交点に、xlookupで会計期間-勘定科目グループをキーにリストから値を取得してみた。

F2セル
=XLOOKUP($E2&"-"&F$1,$A:$A&"-"&$B:$B,$C:$C,0,0)

横縦変換

続いて逆向き、列方向に勘定科目グループを持っているマトリクス表を、縦方向のリストに変換してみる。

SQL

with list as (
select period, '002000' as acgrp_cd, "002000" as sumval from mtrx
  union
select period, '003000' as acgrp_cd, "003000" as sumval from mtrx
  union
select period, '004000' as acgrp_cd, "004000" as sumval from mtrx
  union
select period, '005000' as acgrp_cd, "005000" as sumval from mtrx
  union
select period, '006000' as acgrp_cd, "006000" as sumval from mtrx
  union
select period, '007000' as acgrp_cd, "007000" as sumval from mtrx
  union
select period, '009000' as acgrp_cd, "009000" as sumval from mtrx
  union
select period, '010000' as acgrp_cd, "010000" as sumval from mtrx
  union
select period, '099000' as acgrp_cd, "099000" as sumval from mtrx
)
select
* 
from list 
where sumval is not null;

1列ずつデータを取得した後、UNIONで縦方向につなげた後、値を持っていないレコードを除外してみた。

Excel

これが非常に難しい。。。OFFSET関数を使って座標指定でデータを取得していきます。

最初に会計期間と勘定科目グループの組み合わせの2列を作ります。

1列目の会計期間は勘定科目グループ回並べる必要があるので、行番号を勘定科目グループ回数分=列数分並べていくためL列に

INT((ROW()-2)/列数)

を設定します。

2列目の勘定科目グループはレコードを順番に並べたいので、レコード数分の連番を繰り返し配置するためM列に

=MOD(ROW()-2,9)

を設定します。

これでこんな形の座標が導出できました。

ここからOFFSET関数で値を取得します。

会計期間列（N列）には、（指定された行番号,0）の座標の値をとりたいので、

=OFFSET($A$1,L2+1,0)

同様に勘定科目グループ列（O列）には（0,指定された列番号）の座標の値をとりたいので、

=OFFSET($A$1,0,M2+1)

最後に、値項目（P列）には、（指定された行番号, 指定された列番号）の座標の値をとりたいので、

=OFFSET($A$1,L2+1,M2+1)

を設定します。

この数式を展開してあげれば完成。難しいね。。。

終わりに

縦横変換（リスト→マトリクス）をSQLで、横縦変換（マトリクス→リスト ）をExcelで実現するのは正直 しんどいですね。。。とはいえやらないといけないことが今後もあると思うので、そのときはこのメモを見返したいと思います。走り書きしただけなので、今後自分で見返して分かりにくい点は改善していくかと。

気が向いたら、これをプログラムでやろうとするとどうなるかも試してみるかも。

おまけ

今日のアイキャッチは、縦と横ということで縦横に伸びるビル群を。

参考文献

SQL で縦横変換まとめ（pivot と unpivot） - Qiita

Excel: マトリクスとリストを相互に変換してみる - Qiita