外部結合で行列変換②　（列→行）：繰り返し項目を１列にまとめる　UNION ALL

SQL 指南書集目次 - ray88’s diary
SQL ソート用の列を非表示にして出力する - ray88’s diary
■例題１：以下のようなテーブルを列持ち→行持ちへ変換する

【考え方】
STEP1：以下のようにpersonnel１～３の３つのテーブルを作成する

STEP2：作成した３つのテーブルを縦にマージする
補足：※SQLサーバはフリガナのない漢字に対してはきれいにソートはかからないことが多い。
　　そのため、漢字にソートをかけるためには「フリガナ列」を設けてフリガナ列でソートするしかない。
　　出力結果にソート用の列を表示させないようにするには以下のSQL文のように外部クエリでソートを行う
氏名できれいにソートしたい場合は以下URLを参照
SQL ソート用の列を非表示にして出力する - ray88’s diary
【列から行へ変換するSQL】

/* 列から行への変換：UNION ALLの利用(p.86) */
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;

■上記のSQL文を実行するとchild_1～3の列について、NULLであった場合も出力されてしまう。
　NULLを排除した場合は子供のいない社員「吉田」さんが出力されなくなってしまう。
　NULLを排除しつつ子供のいない社員についても出力するには以下の作業を行う

　STEP1：子供一覧を保持するビューを作成する

/* 子供マスタのビュー(p.87) */
CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
   UNION
   SELECT child_2 FROM Personnel
   UNION
   SELECT child_3 FROM Personnel;

STEP2：社員一覧をマスタとした外部結合を行う

SELECT EMP.employee, CHILDREN.child
  FROM Personnel EMP
       LEFT OUTER JOIN Children
    ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

出力結果
子供マスタと社員テーブルを外部結合している。結合条件でIN述語を指定している。
「Personnel」テーブルの子供１～子供３の列に「Children」ビューの名前と一致する子供がいれ場その名前が、
一致しなければNULLが返る。工藤家と鈴木家に同名の子供「夏子」がいるが、その場合も正しく出力される。

■例題２：例題１で作成したテーブルを元に社員単位で子供の数を集計する

例題２の解答SQL

SELECT EMP.employee, COUNT(Children.child) AS child_count
  FROM Personnel EMP
       LEFT OUTER JOIN Children
    ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
	GROUP BY EMP.employee
	ORDER BY child_count DESC;

【参考】テーブル作成SQL

/* 外部結合で行列変換　その2（列→行）：繰り返し項目を1 列にまとめる */
CREATE TABLE Personnel
 (employee   varchar(32), 
  child_1    varchar(32), 
  child_2    varchar(32), 
  child_3    varchar(32), 
  PRIMARY KEY(employee));

INSERT INTO Personnel VALUES('赤井', '一郎', '二郎', '三郎');
INSERT INTO Personnel VALUES('工藤', '春子', '夏子', NULL);
INSERT INTO Personnel VALUES('鈴木', '夏子', NULL,   NULL);
INSERT INTO Personnel VALUES('吉田', NULL,   NULL,   NULL);