SQLでの1対n結合

Question

下記のようなデータフレームをテーブル３の用に修正を行いたいのですがどうしたら解決できますでしょうか？
ロジックが思いつかずかなり詰まってしまっていて、どなたかご教授お願いします。
テーブル1

Key	value
hoge1	huga1
hoge1	huga2
hoge1	huga3
hoga2	huga1
hoga2	huga1

テーブル2

Key	value
hoge1	test
hoge2	test

テーブル3
|Key|value|test1|test2|test3|
|:--|:--:|
|hoge1|test|huga1|huga2|huga3
|hoge2|test|huga1|huga1|null

Answer 1

• テーブル3のカラムは固定 (例えばtest4が増えたり、test3がなくなったりはしない)
• test1、test2、test3に入れる順序はなんでもよい

ということであれば以下のようなSQLになると思います。

SELECT 
  t2.key AS `key`,
  MAX(t2.value) AS `value`,
  MAX(CASE WHEN MOD(rnk, 3) = 0 THEN t1.value END) AS `test1`,
  MAX(CASE WHEN MOD(rnk, 3) = 1 THEN t1.value END) AS `test2`,
  MAX(CASE WHEN MOD(rnk, 3) = 2 THEN t1.value END) AS `test3`
FROM tbl2 t2
JOIN (
  SELECT *, (ROW_NUMBER() OVER (PARTITION BY t1.key))-1 AS rnk
  FROM tbl1 t1
) t1 ON t2.key = t1.key
GROUP BY t2.key

Answer 2

今のままだと順番が確定しないので、そのとおりを実現するには追加条件が必要です
group_concatのように１カラムで処理するほうが楽だと思います
どうしてもカラム分けをしたいならプロシージャで対応するとよいでしょう

Comments

[bainary]

同じくgroup_concatで処理を行った後に、別々でカラムを用意し処理を行おうと思っていました。
その方法が一番最短でしょうか。。？

[yambejp]

group_concatしてあとにカラムを分けるのは意味がありませんので
やめたほうがいいでしょう。
group_concatした結果の文字列を利用してプログラムでなんとかしてください

回答にも書きましたがどうしてもカラムをわけて処理したいなら
プロシージャです