/*
SQL実践入門P255成績のところを勉強中
*/
生徒に対して、複数の教科があり、生徒の教科の点数が複数存在するので、生徒に対して、横に複数教科が存在するようにしたい。
つまり、行を列に変換したいときにどうするか。
CREATE TABLE ScoreRows( student_id char(4) NOT NULL, subject VARCHAR(8) NOT NULL, score INT, CONSTRAINT pc_ScoreRows PRIMARY KEY(student_id,subject) ) INSERT INTO ScoreRows VALUES('A001','英語',100) INSERT INTO ScoreRows VALUES('A001','国語',58) INSERT INTO ScoreRows VALUES('A001','数学',90) INSERT INTO ScoreRows VALUES('B002','英語',77) INSERT INTO ScoreRows VALUES('B002','国語',60) INSERT INTO ScoreRows VALUES('C003','英語',52) INSERT INTO ScoreRows VALUES('C003','国語',49) INSERT INTO ScoreRows VALUES('C003','社会',100) --成績表のテーブル CREATE TABLE ScoreCols( student_id CHAR(4) NOT NULL, score_en Int, score_nl Int, score_mt Int, CONSTRAINT pk_ScoreCols PRIMARY KEY(student_id) ) INSERT INTO ScoreCols VALUES('A001',NULL,NULL,NULL) INSERT INTO ScoreCols VALUES('B002',NULL,NULL,NULL) INSERT INTO ScoreCols VALUES('C003',NULL,NULL,NULL) INSERT INTO ScoreCols VALUES('D004',NULL,NULL,NULL) --END SELECT * FROM ScoreRows SELECT * FROM ScoreCols --LEFT OUTERにしただけ SELECT * FROM ScoreRows LEFT OUTER JOIN ScoreCols ON ScoreRows.student_id = ScoreCols.student_id
–検討中(1)でもこれだと無駄な行が増えている
--以下は近くなっているが、行で表示されている SELECT ScoreRows.student_id, score_en = ( CASE WHEN subject = '英語' then score ELSE 0 END ), score_nl = ( CASE WHEN subject = '国語' then score ELSE 0 END ), score_mt = ( CASE WHEN subject = '数学' then score ELSE 0 END ), * FROM ScoreCols LEFT OUTER JOIN ScoreRows ON ScoreCols.student_id = ScoreRows.student_id
(2)検討中だいぶ近くなったけど、少々面倒、教科分を連結する必要がある
--生徒に対して横に表示される(しかし、科目が増えると面倒だけど) SELECT a.student_id, b1.score as score_nl, b2.score as score_mt, b3.score as score_en, --b41.score as score_en, * FROM ScoreCols as a LEFT OUTER JOIN (SELECT * FROM ScoreRows WHERE subject = '国語') as b1 ON a.student_id = b1.student_id LEFT OUTER JOIN (SELECT * FROM ScoreRows WHERE subject = '数学') as b2 ON a.student_id = b2.student_id LEFT OUTER JOIN (SELECT * FROM ScoreRows WHERE subject = '英語') as b3 ON a.student_id = b3.student_id LEFT OUTER JOIN (SELECT * FROM ScoreRows WHERE subject = '社会') as b41 ON a.student_id = b41.student_id
つまり、LEFTOUTERは横につないでいくので、テーブルを連結していくことで行が列に与えられることになる。
なのでLEFTOUTERJOINを1列限定にしてあげて、横につないでいくと行が列に置き換えられるイメージになると思う