SQLSERVER勉強中、SQL実践入門P255成績のところ　行を列にピクチャ

/*
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

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

–検討中（１）でもこれだと無駄な行が増えている

--以下は近くなっているが、行で表示されている
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

--以下は近くなっているが、行で表示されている

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

（２）検討中だいぶ近くなったけど、少々面倒、教科分を連結する必要がある

--生徒に対して横に表示される（しかし、科目が増えると面倒だけど）
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

--生徒に対して横に表示される（しかし、科目が増えると面倒だけど）

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列限定にしてあげて、横につないでいくと行が列に置き換えられるイメージになると思う

こちらの記事もどうぞ

P131 SQLパズルすべての製品を購入した人クエリの番号が画像のSELECT出力と同じはず。 /* P131 […]
SQLSERVER パズル63　MINの扱いが難しく理解できない、相関サブクエリパズル63　問題文 dataのまとまりで連続しているnumを表示したい。 MINというと結 […]
SQLSERVER　クエリ、ALL演算子、puzzel11_作業依頼 /* puzzel11_作業依頼 C:完了 W:待機 step_nbrが0でstep_st […]
SQLSERVER　行を列にする方法 […]
sqlの勉強グループの中で連番になっていないデータやグループを探す方法を考え中 sqlの勉強 […]
SQLのお勉強、グループごとにある最大値の行を取得する /*SQLのお勉強、グループごとにある最大値の行を取得する参考サイト https://qii […]