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

 縦持ちデータを横持ちデータに変換するケース(詳しくはこちらを参照のこと)の逆バージョンです。なかなかの難関ですが、正々堂々とSQLで実現する方法があります。

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

 例として右のようなテーブルを用います。これを「氏名」列と「趣味」列の2つの列を持つ縦持ちテーブルに変換します。


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

 結論から先に行きましょう。
 クエリを作成し、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

 データシートビューです。狙ったとおりの変換ができています。



 さて、ちょっとくどい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である行を除き、適宜並べ変えて完成となります。

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

 「縦に並べる」というところから、UNION句を大量に連ねなければ実現できないのではないかとも思われる処理ですが、それを別のアイデアで実現しているのが面白いところです。