横持ちデータを縦持ちデータに変換する

 縦持ちデータを横持ちデータに変換するケース(詳しくはこちらを参照のこと)の逆バージョンです。割とニーズの多い課題ですが、SQLの知識を要しますのでお急ぎでない場合はまずそちらから学習を進めてください。


方法1:ユニオンクエリを利用する方法

f:id:accs2014:20160820210500p:plain:right:w400

 基本的には列の数だけSELECT句をUNIONでつなぐ、という素直な方法です。
 例として右のようなテーブルを用います。これを「氏名」列と「趣味」列の2つの列を持つ縦持ちテーブルに変換します。


f:id:accs2014:20170904160754p:plain:right:w500

 SQLビューで次のように記入します。



SELECT [ユニオン].氏名, [ユニオン].趣味
FROM (SELECT 氏名,趣味1 AS 趣味 FROM 趣味テーブル UNION SELECT 氏名,趣味2 FROM 趣味テーブル UNION SELECT 氏名,趣味3 FROM 趣味テーブル UNION SELECT 氏名,趣味4 FROM 趣味テーブル)  AS ユニオン
WHERE [ユニオン].趣味 Is Not Null
ORDER BY [ユニオン].氏名, [ユニオン].趣味;

 基本的には列の数だけSELECT句をUNIONでつなぐだけです。ただし、SELECT~UNION~だけだと並べ替えの指定(ORDER BY)のあたりがうまくいきませんので、一旦FROM句内のサブクエリとして作成し、そこから2つの列を抽出して並べ替えています。
 なお、この例では同一の行(氏名、趣味ととも同一の行)が出力されることを想定していませんが、そうなるのを許容したい場合はUNIONの代わりにUNION ALLを用います。


f:id:accs2014:20170904160748p:plain:right:w200

 データシートビューです。


f:id:accs2014:20170904160745p:plain:right:w500

 さて、別の例になりますが、「趣味」の並べ替えについて、もとのテーブルの列の順番(趣味1,趣味2…)によるものとしたい、という場合は次のようにします。

SELECT [ユニオン].氏名, [ユニオン].趣味
FROM (SELECT 氏名,趣味1 AS 趣味,1 AS 連番 FROM 趣味テーブル UNION SELECT 氏名,趣味2,2 FROM 趣味テーブル UNION SELECT 氏名,趣味3,3 FROM 趣味テーブル UNION SELECT 氏名,趣味4,4 FROM 趣味テーブル)  AS ユニオン
WHERE [ユニオン].趣味 Is Not Null
ORDER BY [ユニオン].氏名, [ユニオン].連番;

 サブクエリ(SELECT~UNION~)内に「連番」列を付加し、趣味として何番目の列にあったかを値として記録し、その値により並べ替えを行っています。


f:id:accs2014:20170904160738p:plain:right:w200

 データシートビューです。


方法2:直積を利用する方法

f:id:accs2014:20160820210500p:plain:right:w400

 幾分テクニカルな方法です。
 用いるテーブルは上記のものと同じです。これを「氏名」列と「趣味」列の2つの列を持つ縦持ちテーブルに変換します。


f:id:accs2014:20160820213059p:plain:right:w500

 クエリを作成し、SQLビューで次のように入力します。



SELECT 直積.氏名, Choose(直積.連番,直積.趣味1,直積.趣味2,直積.趣味3,直積.趣味4) AS 趣味
FROM (SELECT 趣味テーブル.氏名, [ダミークエリ].連番, 趣味テーブル.趣味1, 趣味テーブル.趣味2, 趣味テーブル.趣味3, 趣味テーブル.趣味4 FROM 趣味テーブル, (SELECT 1 AS 連番 FROM 趣味テーブル UNION SELECT 2 FROM 趣味テーブル UNION SELECT 3 FROM 趣味テーブル UNION SELECT 4 FROM 趣味テーブル)  AS ダミークエリ)  AS 直積
WHERE Choose(直積.連番,直積.趣味1,直積.趣味2,直積.趣味3,直積.趣味4) Is Not Null
ORDER BY 直積.氏名, 直積.連番;


f:id:accs2014:20160820213100p:plain:right:w200

 データシートビューです。
 なお、この方法では、方法1の2番目の例と同じく、「趣味」の並べ替えは、もとのテーブルの列の順番(趣味1,趣味2…)によるものとなっています。



 さて、ちょっとくどいSQLでしたが動作を補足します。
 まず、次のサブクエリについてです。

(SELECT 1 AS 連番 FROM 趣味テーブル UNION SELECT 2 FROM 趣味テーブル UNION SELECT 3 FROM 趣味テーブル UNION SELECT 4 FROM 趣味テーブル)  AS ダミークエリ

 このサブクエリにより、趣味テーブルの内容にかかわらず(趣味テーブルに1行以上のデータがないといけませんが)以下のようなデータが生成されます。連番が4までなのは趣味の列数が4つだからであり、横持ちになっている列数に応じて変える必要があります。
 ただ、サブクエリにする必要はなく、普通のテーブルとして作成しても問題ありませんし、そちらの方が簡単ではあります。

ダミークエリ
連番
1
2
3
4

 次に、最初のFROM句から「AS 直積」までの部分により、当初のテーブルとこのサブクエリの直積である次のようなデータが生成されます(便宜上並び順を変えて表示しています)。

直積
氏名連番趣味1趣味2趣味3趣味4
井上1将棋囲碁料理ボウリング
井上2将棋囲碁料理ボウリング
井上3将棋囲碁料理ボウリング
井上4将棋囲碁料理ボウリング
山田1囲碁盆栽
山田2囲碁盆栽
山田3囲碁盆栽
山田4囲碁盆栽
鈴木1マラソンテニス将棋
鈴木2マラソンテニス将棋
鈴木3マラソンテニス将棋
鈴木4マラソンテニス将棋

 最後にもう一つのポイントです。Choose関数のはたらきにより、連番の値に応じて抽出する列を替えながら趣味の値を抽出します。

直積
氏名連番趣味1趣味2趣味3趣味4
井上1将棋囲碁料理ボウリング
井上2将棋囲碁料理ボウリング
井上3将棋囲碁料理ボウリング
井上4将棋囲碁料理ボウリング
山田1囲碁盆栽
山田2囲碁盆栽
山田3囲碁盆栽
山田4囲碁盆栽
鈴木1マラソンテニス将棋
鈴木2マラソンテニス将棋
鈴木3マラソンテニス将棋
鈴木4マラソンテニス将棋

 結果的に次のようなデータが抽出されますので、あとは趣味がNullである行を除き、適宜並べ変えて完成となります。

氏名趣味
井上将棋
井上囲碁
井上料理
井上ボウリング
山田囲碁
山田盆栽
山田
山田
鈴木マラソン
鈴木テニス
鈴木将棋
鈴木

 実用性では最初の方法の方が勝りますが、別のアイデアで実現しているのが面白いところです。