外部結合で行列変換①　（行→列）：クロス表を作る

[SQL 指南書集目次 - ray88’s diary
■CASE式でクエリ結果をクロス表にする方法があるが、同じことを外部結合的な発想で実現する

【例題】
以下のように、社員が受講した研修コースを管理するテーブルを行列変換する

■解答例１：外部結合の利用
　　　　　※解答１のSQLは他の講座が増えた際の改修時にはSELECT句とFROM句の２か所の
　　　　　　　修正が必要になるので、改修の手間がかかりコードが長大になることと、
　　　　　　　列が増えるにつれパフォーマンスが悪化するのが欠点。より短いコードで
　　　　　　　記述するには解答２と３を参照
【考え方】
STEP１：「Courses」テーブルから氏名マスタと各講座ごとのC0～C3の４つの集合を作る
　　　　※SQLの中で名前を与えればテーブルもビューも等しく「集合」として存在することになる。

STEP2：C0をマスタとして、C1～C3を順番に外部結合していくと、受講した口座の列には受講者の名前が、未受講の列にはNULLが表示される
STEP3：CASE式で受講済みを「〇」に変換する（受講者名→〇へ変換）

【解答SQL文１】

/* クロス表を求める水平展開　その1：外部結合の利用(p.83) */
SELECT C0.name,
       CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "SQL入門",
       CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX基礎",
       CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java中級"
  FROM  (SELECT DISTINCT name FROM  Courses) C0
    LEFT OUTER JOIN
    (SELECT name FROM Courses WHERE course = 'SQL入門' ) C1
    ON  C0.name = C1.name
      LEFT OUTER JOIN
        (SELECT name FROM Courses WHERE course = 'UNIX基礎' ) C2
        ON  C0.name = C2.name
          LEFT OUTER JOIN
            (SELECT name FROM Courses WHERE course = 'Java中級' ) C3
            ON  C0.name = C3.name;

■解答例２：スカラサブクエリの利用
　　　　　※解答２のSQL文は講座の増減があった場合もSELECT句の変更のみで済むため、
　　　　　　仕様変更に強い。点はSELECT句でスカラ・クエリと相関クエリを使用するため、
　　　　　　パフォーマンスがあまりよくない。より簡潔なコード例は「解答３」のSQL文を参照。
【考え方】
STEP1：元のテーブルより以下のようにC0～C3の集合を作成する

STEP2：C0をマスタとし、「name」をキーとしてC1, C2, C3を結合する

STEP3：C1, C2, C3 に出力された氏名を「〇」に変換する

【解答SQL文２】

/* 水平展開　その2：スカラ・サブクエリの利用(p.84) */
SELECT  C0.name
  ,(SELECT '○'
     FROM Courses C1
    WHERE course = 'SQL入門'
      AND C1.name = C0.name) AS "SQL入門"
  ,(SELECT '○'
     FROM Courses C2
    WHERE course = 'UNIX基礎'
      AND C2.name = C0.name) AS "UNIX基礎"
  ,(SELECT '○'
     FROM Courses C3
    WHERE course = 'Java中級'
      AND C3.name = C0.name) AS "Java中級"
  FROM (SELECT DISTINCT name FROM Courses) C0;

■解答３：CASE式を入れ子にする
　　　　　※CASE式はSELECT句で集約関数の中にも外にも書くことが可能。
　　　　　　SELECｔ句では集約関数もスカラ値に評価されるので、定数や列と同じように評価される。
【考え方】
　対象講座条件にしてレコードを絞り、氏名でグループ化して集計する。
　集計結果が１以上の場合について「〇」におきかえ、それ以外の場合はNULLを表示する
　各講座にたいして同じようにして講座の数分列を作成する

【解答SQL文３】

/* 水平展開　その3：CASE式を入れ子にする(p.85) */
SELECT  name,
        CASE WHEN SUM(CASE WHEN course = 'SQL入門' THEN 1 ELSE NULL END) >= 1
             THEN '○' ELSE NULL END AS "SQL入門",
        CASE WHEN SUM(CASE WHEN course = 'UNIX基礎' THEN 1 ELSE NULL END) >= 1
             THEN '○' ELSE NULL END AS "UNIX基礎",
        CASE WHEN SUM(CASE WHEN course = 'Java中級' THEN 1 ELSE NULL END) >= 1
             THEN '○' ELSE NULL END AS "Java中級"
  FROM Courses
 GROUP BY name;

【参考】
テーブル作成SQL

/* 外部結合で行列変換　その1（行→列）：クロス表を作る */
CREATE TABLE Courses
(name   VARCHAR(32), 
 course VARCHAR(32), 
 PRIMARY KEY(name, course));

INSERT INTO Courses VALUES('赤井', 'SQL入門');
INSERT INTO Courses VALUES('赤井', 'UNIX基礎');
INSERT INTO Courses VALUES('鈴木', 'SQL入門');
INSERT INTO Courses VALUES('工藤', 'SQL入門');
INSERT INTO Courses VALUES('工藤', 'Java中級');
INSERT INTO Courses VALUES('吉田', 'UNIX基礎');
INSERT INTO Courses VALUES('渡辺', 'SQL入門');

【一時置きテストコード】

SELECT  name
	    ,SUM(CASE WHEN course = 'SQL入門' THEN 1 ELSE NULL END)AS "SQL入門"
		,SUM(CASE WHEN course = 'UNIX基礎' THEN 1 ELSE NULL END)AS "SQL入門"
		,SUM(CASE WHEN course = 'Java中級' THEN 1 ELSE NULL END)AS "Java中級"
--         CASE WHEN SUM(CASE WHEN course = 'SQL入門' THEN 1 ELSE NULL END) >= 1
--              THEN '○' ELSE NULL END AS "SQL入門",
--         CASE WHEN SUM(CASE WHEN course = 'UNIX基礎' THEN 1 ELSE NULL END) >= 1
--              THEN '○' ELSE NULL END AS "SQL入門",
--         CASE WHEN SUM(CASE WHEN course = 'Java中級' THEN 1 ELSE NULL END) >= 1
--              THEN '○' ELSE NULL END AS "Java中級"
  FROM Courses
 GROUP BY name;