SQLの書き方について

Question

SQL初心者です。試行錯誤しながらいじっているのですが下記ケースでうまい書き方がわからないためどなたかヒント等ご教示いただけると助かります。関連する2つの質問があります

◇環境：
MariaDB（5.5.68）環境

◇質問１
下記のテーブルAがあった時にBのように縦軸を名前、横軸をカテゴリ毎に種類を並べたいですが
どんなやり方があるでしょうか

テーブルA（入力）：
Name Category Syurui
ーーーーーーーーーーーーーーーーーー
Aさん ラーメン 味噌
Bさん ラーメン 醤油
Cさん ラーメン 醤油
Dさん ラーメン 塩
Eさん ラーメン 味噌
Aさん お寿司 はまち
Bさん お寿司 いか
Cさん お寿司 まぐろ
Dさん お寿司 まぐろ
Eさん お寿司 たまご
Aさん ケーキ ショートケーキ
Bさん ケーキ モンブラン
Cさん ケーキ ショートケーキ
Dさん ケーキ チーズケーキ
Eさん ケーキ チーズケーキ

テーブルB（期待出力）：
名前 ラーメン お寿司 ケーキ
ーーーーーーーーーーーーーーーーーーー
Aさん 味噌 はまち ショートケーキ
Bさん 醤油 いか モンブラン
Cさん 醤油 まぐろ ショートケーキ
Dさん 塩 まぐろ チーズケーキ
Eさん 味噌 たまご チーズケーキ

【やってみたこと１】
SELECT
CASE WHEN Category='ラーメン' THEN Syrui ELSE NULL END AS 'ラーメン'
CASE WHEN Category='お寿司' THEN Syrui ELSE NULL END AS 'お寿司'
CASE WHEN Category='ケーキ' THEN Syrui ELSE NULL END AS 'ケーキ'
FROM
A

→これで横に並べることはできたのですが、縦がAの行数のままとなってしまい冗長です
GroupBy等で行を縮退できないものかとも思ったのですがうまくいきませんでした。。
（結果は下記）
名前 ラーメン お寿司 ケーキ
ーーーーーーーーーーーーーーーーーーー
Aさん 味噌 NULL NULL
Bさん 醤油 NULL NULL
Cさん 醤油 NULL NULL
Dさん 塩 NULL NULL
Eさん 味噌 NULL NULL
Aさん NULL はまち NULL
Bさん NULL いか NULL
Cさん NULL まぐろ NULL
Dさん NULL まぐろ NULL
Eさん NULL たまご NULL
Aさん NULL NULL ショートケーキ
Bさん NULL NULL モンブラン
Cさん NULL NULL ショートケーキ
Dさん NULL NULL チーズケーキ
Eさん NULL NULL チーズケーキ
→縦が冗長

【やってみたこと２】
テーブルAをINNNER JOINで複数つなげ、WHERE句で各々「Category＝’ラーメン’」などと絞る

→これで所望のテーブルBができることはできましたが、、、、
実際のテーブルAは、複数テーブルをINNNERJOINでつなぎ合わせた結果で作られているため
「テーブルAを作る部分の複数のコード」がｘN回登場しており可読性が極めて悪いです
なにかほかにうまい記述方法はありますでしょうか

◇質問２
課題設定は1と同じなのですが、やってみたこと２を実行してみると、INNERJOINで
2つまでは直ぐに結果が返ってくる（1秒以内）のですが、３つつなげた時点で一気に遅くなり（2分程度）このままでは使い物にならない（さらにNを増やしたい）ため速度UPが期待できる書き方にしたいです

【やってみたこと】
今回のシチュエーションでは、「テーブルAを作る部分の複数のコード」が基本全く同じなので、
CREATE TEMPORARY TABLEで先頭でベーステーブル（SELECT出力をそのまま突っ込む）を作って、それを複数回利用する形にすれば　可読性、スピードともに速くなる（≒実際のテーブルAが複数テーブルのINNNERJOIN句で作られている部分が1回だけになるためことを期待）のではと思ったのですが、、　自分の環境では構文エラーとなってしまいます。デバッグのために超シンプルなテーブル、クエリと合わせてＭａｎｕａｌ読み読みしながらやっても同じように構文エラーとなります。
→MariaDB（5.5.68）ではこのような使い方はサポート外だったりしますか？
→ほかに簡単な手法で高速化が図れそうなアイデアはないでしょうか
（インデックス利用というのは勉強中でまだよくわからないのと、テーブルＡが複数テーブルから作られているので理解が追い付かなそうなので、それ以外の簡単なやり方でいい手があれば）

長々とすいません。詳しい方、ぜひアドバイスいただけたら幸いです
よろしくお願いいたします

Answer 1

SELECT 
Name AS "名前",
MAX(Ramen) AS "ラーメン",
MAX(Sushi) AS "お寿司", 
MAX(Cake) AS "ケーキ"
FROM (
SELECT
Name AS Name,
CASE WHEN category='ラーメン' THEN syurui ELSE NULL END AS Ramen,
CASE WHEN category='お寿司' THEN syurui ELSE NULL END AS Sushi,
CASE WHEN category='ケーキ' THEN syurui ELSE NULL END AS Cake
FROM
A
) AS tmp
GROUP BY Name;

これでできると思いますが、どうでしょうか？
パフォーマンス的に他のやり方もあると思いますが、参考までに。

Comments

[masa0929]

PlugOut777さま：
早速ありがとうございます！
■結論から言うと
・やりたいことが無事にできた
・自分で考えた「INNNEJOINで複数繋げたTableを、INNERJOINでN回つなげる」といったことが不要なので、可読性、速度ともに問題ありません
　→満額回答です

■ただMAXを使ってうまくいく”からくり”が全く理解できません。。
・MAXだけだと１行しか出てこない（ここは想定通り）
・GROUPBYだけだと１列しか出てこない（ここは想定通り）
・２つ合わせると所望の結果が出る(ここがまったくわからない。。）

もしよかったら後学のためにご教示いただけると大変嬉しいです

追記＞
やりたいことは解決しましたのでベストアンサーでCloseしたいと思ったのですが、今回遭遇した質問２の方の情報を勉強のためもう少し待ってみたいと思います

[PlugOut777]

> ただMAXを使ってうまくいく”からくり”が全く理解できません。。
MAXは集合関数というもので、GROUP BY Nameを使って一まとめにしたレコード群に対してどのような集計を行うかを指定するものです。
わかりやすいようにAさんだけのレコードで解説すると、まずサブクエリの結果は以下の様になります。

名前
Aさん 味噌 NULL NULL
Aさん NULL はまち NULL
Aさん NULL NULL ショートケーキ

これにGROUP BYを使うと、例えばAさんの[ラーメン]は[味噌 NULL NULL]の3つになります。
MAXはGROUP BYされたもので指定されたカラムの中からNULL以外で値の入っていないものの中から最大値を取る関数ですが、文字列に対しても使用した場合は文字コードでの大小比較となります。
従って、NULLではない[味噌]が選択されることとなるわけです。
他の[お寿司]と[ケーキ]についても同様です。

[masa0929]

PlugOut777さま：
#うまく投稿できなかったようなので改めて。２重になってたらごめんなさい

なるほどです。
GROUP BYしたときに1hot状態になるように書いているので、MAXによって１hotの要素だけが抽出されるということなのですね。よく理解できました

このような記述テクニックがあるとは、自分ではまったく想像できませんでした。
大変勉強になります。重ね重ね、丁寧にありがとうございました！！

＃質問２のほうはちょっと別な質問にも思えてきたので、こちらベストアンサーとさせていただ「回答済み」とさせていただこうと思います