Microsoft MCSA SQL Server 2016 (70-761)むぼーな挑戦10/PIVOT・ UNPIVOT どうしてもわからない。。。

はい、ねこです。

動画やったりして、結構満足してました。。。

PIVOTの超簡単説明

UNPIVOTの超簡単説明

。。。あんだけわかった気になってたのに、やっぱり全然わからんPIVOT・UNPIVOTです。

ここに過去問があります。

【PIVOT・UNPIVOT】

You have a table named Cities that has the following two columns: City ID and CityName.

Citiesというテーブルがあり、City IDとCityNameからできてます。ふむふむ。リファレンステーブルやな。

The CityID column uses the int data type, and CityName uses nvarchar(max).

City IDがintで、CityNameがnvarchar(max)。よくある系ですな。

You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities.

RawSurveyテーブルというのがあり、それぞれの行に『question質問の種類』と『number of persons回答者数』が『city４つ』ごとにあります。

The table contains the following representative data:下記の通り

A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table.
SurveyReportというテーブルがあり、CityIDとQuestionIDとRawCount（RawSurveyテーブルから値を持ってくる）とあります。うぅ～ん、全部で三つのコラム。

You need to write a Transact-SQL query to meet the following requirements:下記の条件に合うT-SQLを書きなさい。

– Retrieve data from the RawSurvey table in the format of the Survey Report table. SurveyReportの形式に変更してRawSurveyテーブルから値を持ってきなさい。すると、元データのRawSurveyテーブルには『question』『number of persons』『city4つ』あって、CityIDとQuestionID、そしてRawCount（RawSurveyテーブルから値を持ってくる）のSurveyReportテーブルに直さなければならない。

 

– The City ID must contain the City ID of the city that was surveyed.　CityIDはRawSurveyテーブルのCityのCityIDからもってくるように。

– The order of cities in all SELECT queries must match the order in the RawSurvey table.　RawSurveyテーブルのCityの順番通りにもってくること。

– The order of cities in all IN statements must match the order in the RawSurvey table.　全ての『IN』文にはRawSurveyテーブルのCityが順番通りであること。

。。。もうわからん。。。なきたいなぁ。

。。。ちょっと休憩。Dr.コトーの島、第六巻、和田さん結婚できてよかった。何回読んでもいい漫画だにゃぁーっ！さてと。

SELECT CityId, QuestionID, RawCount 

FROM　SurveyReport t1

UNPIVOT

(RawCount FOR CityID IN (Tokyo,Boston,London,New York)) t1

LEFT JOIN Cities t2 on t1.CityID = t2.CityID

 

いかん、なにやってんのか全然わからん。。。

答え見ます。負け犬、いや、負け猫です。

＜答えその一＞

SELECT CityId, QuestionId, RawCount
FROM (SELECT QuestionId, Tokyo, Boston, London, NewYork FROM RawSurvey) AS t1
UNPIVOT (RawCount FOR CityName IN (Tokyo, Boston, London, NewYork)) AS t2
JOIN Cities ON t2.CityName = Cities.CityName;

＜答えその二＞エリアス名だけの違いか。。。どっちが正しいのかな。。。

SELECT CityId, QuestionId, RawCount
FROM (SELECT QuestionId, Tokyo, Boston, London, NewYork FROM RawSurvey) t2
UNPIVOT　(RawCount　FOR CityName IN (Tokyo, Boston, London, NewYork)) AS t2
JOIN Cities t1 ON t2.CityName = t1.CityName;

＜答えその三＞これまた、思い切ってますな。“New York” はコラム名だとスペースがあって均一じゃないからか？

SELECT CityID, QuestionID, RawCount FROM Cities AS t1
(SELECT Tokyo, Boston, London, “New York” FROM Rawsurvey) p
UNPIVOT
(Rawcount FOR CityName IN (‘Tokyo’,’Boston’,’London’,’New York’) AS t2
JOIN t2 ON t1.CityName = t2.cityName

＜ねこの答え＞もう、救いようがねーな。LEFT JOINって。。。

SELECT CityID, QuestionID, RawCount FROM SurveyReport t1
UNPIVOT
(RawCount FOR CityID IN (Tokyo,Boston,London,New York)) t1
LEFT JOIN Cities t2 on t1.CityID = t2.CityID

...これは検証するしかねーな。

【検証！】

CREATE TABLE Cities (
CityId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CityName NVARCHAR(MAX) NOT NULL
);

INSERT INTO Cities VALUES
(‘Tokyo’), (‘Boston’), (‘London’), (‘NewYork’);

CREATE TABLE RawSurvey (
QuestionId NCHAR(2) NOT NULL PRIMARY KEY CLUSTERED,
Tokyo INT NOT NULL,
Boston INT NOT NULL,
London INT NOT NULL,
NewYork INT NOT NULL
);

INSERT INTO RawSurvey VALUES
(‘Q1’, 1, 42, 48, 51),
(‘Q2’, 22, 39, 58, 42),
(‘Q3’, 29, 41, 61, 33),
(‘Q4’, 62, 70, 60, 50),
(‘Q5’, 63, 31, 41, 21),
(‘Q6’, 32, 1, 16, 34);

とにかく、形にしようと、『CityID』抜きでやってみた。

Derived tableでUNPIVOTに必要なテーブルを整えてやります。

*A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known.

derived tableは、クエリのFROM句に表示されるテーブル式です。 エイリアス名がわかる前に別の句がSQLトランスレータによって処理されるため、列エイリアスを使用できない場合は、derived tableテーブルを適用できます。

Select QuestionID, RawCount
From (Select QustionID, Tokyo, Boston, London, NewYork From RawSurvey) as t1
UNPIVOT
RawCount FOR CityName IN (Tokyo, Boston, London, NewYork) as t1

『RawCount』と『CityName』は今のところ、ただの入れ物に過ぎない。『RawCount１１１１』や『CityXXXName』であってもかまわないということ。まだ何も紐づいてないからね　違うぞ！これはUNPIVOTのために暫定的につけているなまえだから、あとで cities.CityName = t2.CityxxxNameとしてもcitiesテーブルのCityNameと比較できるんだよね。だからオッケー。

【検証結果！！！】

どうやら、これでいけそうでした。derived table必要なかったのか？！まぁ、整える必要はなかったようですね。

SELECT CityID, QuestionID, RawCount
FROM RawSurvey as t1
UNPIVOT (RawCount FOR CityName IN (Tokyo, Boston, London, NewYork)) as t1
JOIN cities on t1.CityName = Cities.CityName

もう、茶しばいて寝ます。ねこは早寝早起きのママねこです。

おやちゅみ～。