外部結合で行列変換 その2（列→行）：繰り返し項目を1列にまとめる
前問では行から列へ変換しました。それなら今度は、列から行へ変換したくなるのが人情というもの。例えば次のようなDBエンジニア泣かせのテーブルを考えましょう。
| 社員(employee) | 子供1(child_1) | 子供2(child_2) | 子供3(child_3) |
| 赤井 | 一郎 | 二郎 | 三郎 |
| 工藤 | 春子 | 夏子 | |
| 鈴木 | 夏子 | ||
| 吉田 |
皆さんも、1度は目にしたことがあるでしょう。COBOLなどで使われているファイルを入力データとする場合に、安易にそのフォーマットに引きずられると、こういうテーブルが出来上がります。このテーブルのどの辺が泣かせどころかという点には、今は立ち入りません。こういうテーブルは「行持ち」の形式へ変換するのが基本です。最も簡単なやり方は、UNION ALLを使います。
SELECT employee, child_1 AS child FROM Personnel UNION ALL SELECT employee, child_2 AS child FROM Personnel UNION ALL SELECT employee, child_3 AS child FROM Personnel;
employee child ---------- ------- 赤井 一郎 赤井 二郎 赤井 三郎 工藤 春子 工藤 夏子 工藤 鈴木 夏子 鈴木 鈴木 吉田 吉田 吉田
UNION ALLは重複行を排除しないので、子供のいない吉田氏についてもきっちり3行出力されます。テーブルへ格納するならchild列がNULLの行を排除した形にするのが良いでしょう。
また時として、子供のいない吉田氏も残した次のようなリストが欲しい場合もあります。
| 社員(employee) | 子供(child) |
| 赤井 | 一郎 |
| 赤井 | 二郎 |
| 赤井 | 三郎 |
| 工藤 | 春子 |
| 工藤 | 夏子 |
| 鈴木 | 夏子 |
| 吉田 |
このケースでは、単純にchild列がNULLの行を除外するわけにはいきません。方法はいくつか考えられますが、ここでは社員一覧をマスタとした外部結合を使いましょう。結合条件に注目してください。
SELECT EMP.employee, CHILDREN.child FROM Personnel EMP LEFT OUTER JOIN (SELECT child_1 AS child FROM Personnel UNION SELECT child_2 AS child FROM Personnel UNION SELECT child_3 AS child FROM Personnel) CHILDREN ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);
インライン・ビュー「CHILDREN」は、子供全員を含む集合、いわば「子供マスタ」です（例によって、この集合が最初からテーブルとして用意されているなら、それを使ってください）。それと社員テーブルを外部結合しているわけですが、重要なのは結合条件をIN述語で指定していることです。これによって、「Personnel」テーブルの子供1～子供3の列に「CHILDREN」ビューの名前と一致する子供がいればその名前が、一致しなければNULLが返ります。工藤家と鈴木家には同名の子供「夏子」がいますが、その場合でも正しく動作します。
クロス表で入れ子の表側を作る
統計表を作成する業務では、表側や表頭を入れ子にした表を作りたいという要望がよく発生します。例えば、県別・年齢階級別・性別の人口データを保持する「TblPop」テーブルから、次のようなクロス表を作るケースです。
| 年齢階級（age_class） | 年齢（age_range） |
| 1 | 21～30歳 |
| 2 | 31～40歳 |
| 3 | 41～50歳 |
| 性別コード（sex_cd） | 性別（sex） |
| m | 男 |
| f | 女 |
| 県名（pref_name） | 年齢階級（age_class） | 性別コード（sex_cd） | 人口（population） |
| 秋田 | 1 | m | 400 |
| 秋田 | 3 | m | 1000 |
| 秋田 | 1 | f | 800 |
| 秋田 | 3 | f | 1000 |
| 青森 | 1 | m | 700 |
| 青森 | 1 | f | 500 |
| 青森 | 3 | f | 800 |
| 東京 | 1 | m | 900 |
| 東京 | 1 | f | 1500 |
| 東京 | 3 | f | 1200 |
| 千葉 | 1 | m | 900 |
| 千葉 | 1 | f | 1000 |
| 千葉 | 3 | f | 900 |
| 東北 | 関東 | ||
| 21～30歳 | 男 | 1100 | 1800 |
| 女 | 1300 | 2500 | |
| 31～40歳 | 男 | ||
| 女 | |||
| 41～50歳 | 男 | 1000 | |
| 女 | 1800 | 2100 |
問題の要点は、「TblPop」テーブルには年齢階級「2」のデータが1行もないのだけれど、結果にはその階級も含めて、6行固定で出力することです。表側固定となれば外部結合の出番ですが、表側を入れ子にするには一ひねり必要です。今回は年齢階級と性別が表側なので、「TblAge」と「TblSex」をマスタに使います。
基本的な考え方としては、この2つのテーブルをマスタにして外部結合するのですが、単純に外部結合を繰り返すだけではうまくいきません。
SELECT MASTER1.age_class AS age_class, MASTER2.sex_cd AS sex_cd, DATA.pop_tohoku AS pop_tohoku, DATA.pop_kanto AS pop_kanto FROM (SELECT age_class, sex_cd, SUM(CASE WHEN pref_name IN ('青森', '秋田') THEN population ELSE NULL END) AS pop_tohoku, SUM(CASE WHEN pref_name IN ('東京', '千葉') THEN population ELSE NULL END) AS pop_kanto FROM TblPop GROUP BY age_class, sex_cd) DATA RIGHT OUTER JOIN TblAge MASTER1 --外部結合1：年齢階級マスタと結合 ON MASTER1.age_class = DATA.age_class RIGHT OUTER JOIN TblSex MASTER2 --外部結合2：性別マスタと結合 ON MASTER2.sex_cd = DATA.sex_cd;
age_class sex_cd pop_tohoku pop_kanto ----------- -------- ------------ ----------- 1 m 1100 1800 1 f 1300 2500 3 m 1000 3 f 1800 2100
結果を見ての通り、年齢階級「2」の行が現れません。これは困りました。せっかく外部結合を使ったのに、なぜうまくいかないのでしょう？ それは、「TblPop」テーブルに年齢階級「2」のレコードが1行もなかったからです。……あれ、でも外部結合ってそういう場合でも定型的な結果を得るための技術じゃなかったっけ？
そう、確かにそのとおりです。その証拠に、最初の年齢階級マスタとの結合を終えた時点では、年齢階級「2」のレコードもちゃんと結果に含まれているのです。
SELECT MASTER1.age_class AS age_class, DATA.sex_cd AS sex_cd, DATA.pop_tohoku AS pop_tohoku, DATA.pop_kanto AS pop_kanto FROM (SELECT age_class, sex_cd, SUM(CASE WHEN pref_name IN ('青森', '秋田') THEN population ELSE NULL END) AS pop_tohoku, SUM(CASE WHEN pref_name IN ('東京', '千葉') THEN population ELSE NULL END) AS pop_kanto FROM TblPop GROUP BY age_class, sex_cd) DATA RIGHT OUTER JOIN TblAge MASTER1 ON MASTER1.age_class = DATA.age_class;
age_class sex_cd pop_tohoku pop_kanto ----------- -------- ------------ ----------- 1 m 1100 1800 1 f 1300 2500 2 --年齢階級「2」も存在する 3 m 1000 3 f 1800 2100
しかし、です。ここが核心なので注意してほしいのですが、年齢階級「2」は確かに「TblAge」から取得できます。しかし、それに対応する「TblPop」テーブルの性別コードはNULLになるのです。これは考えてみれば当然のことで、「TblPop」テーブルに年齢階級「2」のデータは存在しないのだから、その性別に「m」も「f」もありません。NULL以外は入りようがないのです。そのため、次の性別マスタとの外部結合において、結合条件が「ON MASTER2.sex_cd = NULL」となってしまい、結果はunknownです（この真理値の意味は『3値論理とNULL』を参照）。従って、最終結果には絶対に年齢階級「2」の行が現れません。結合するテーブルの順番を逆にしてもうまくいきません。
では、どうすれば入れ子の表側を正しく作れるでしょう。答えは、外部結合を2度することが許されないなら、1度で済ませてしまえばよい、というものです。
SELECT MASTER.age_class AS age_class, MASTER.sex_cd AS sex_cd, DATA.pop_tohoku AS pop_tohoku, DATA.pop_kanto AS pop_kanto FROM (SELECT age_class, sex_cd, SUM(CASE WHEN pref_name IN ('青森', '秋田') THEN population ELSE NULL END) AS pop_tohoku, SUM(CASE WHEN pref_name IN ('東京', '千葉') THEN population ELSE NULL END) AS pop_kanto FROM TblPop GROUP BY age_class, sex_cd) DATA RIGHT OUTER JOIN (SELECT age_class, sex_cd FROM TblAge CROSS JOIN --クロス結合でマスタ同士の直積を作る TblSex ) MASTER ON MASTER.age_class = DATA.age_class AND MASTER.sex_cd = DATA.sex_cd;
age_class sex_cd pop_tohoku pop_kanto ----------- -------- ------------ ----------- 1 m 1100 1800 1 f 1300 2500 2 m 2 f 3 m 1000 3 f 1800 2100
これでしっかり6行が得られました。「TblPop」がどれほど不完全なテーブルでも、結果の表側は常に6行固定で得られます。トリックは「TblAge」と「TblSex」をクロス結合して、「MASTER」という直積を作ることです。
| 年齢階級（age_class） | 性別コード（sex_cd） |
| 1 | m |
| 1 | f |
| 2 | m |
| 2 | f |
| 3 | m |
| 3 | f |
すると、外部結合はこの「MASTER」ビューに対する1度だけで済みます。つまり、表側を入れ子にするときは、その形のマスタをあらかじめ用意してやればよいのです。3レベル以上の入れ子の場合も、同じやり方で拡張できます。
なお、CROSS JOIN構文を持っていないDBMSの場合は、「FROM TblAge, TblSex」のように、結合条件の指定なしでテーブルを並べればクロス結合と同じ演算になります。
