mySQLのテーブルの縦横変換（横→縦）において、キーの数が多い場合の対応

Question

以下の図のように、縦持ちテーブルを横持ちテーブルに変換したいと思っています（テーブルやスクリプトは以下リンクから引用させていただいています）。

SQL で縦横変換まとめ（pivot と unpivot）

対応方法としてこのページにあるCASE WHENを使って変換する方法はイメージがつくのですが、自分の場合、このキーが100個くらいあります（c1〜c100）。力技で100行のクエリーを書けなくはないのですが、キーが増える度にクエリも書き換えなければならず、不便だと感じています。

Pythonで対応する方法もありますが、これをmySQLで実装する方法はありますでしょうか？
そもそもテーブルの持ち方を変えた方がいいんじゃない？といった指摘でも助かります。

縦持ちテーブル

uid	key	value
101	c1	11
101	c2	12
101	c3	13
102	c1	21
102	c2	22
102	c3	23

横持ちテーブル

uid	c1	c2	c3
101	11	12	13
102	21	22	23

SELECT uid,
       max(CASE WHEN key = 'c1' THEN value END) AS c1,
       max(CASE WHEN key = 'c2' THEN value END) AS c2,
       max(CASE WHEN key = 'c3' THEN value END) AS c3
FROM vtable
GROUP BY uid
;

Comments

[yambejp]

uidとkeyの組み合わせはユニークだという担保はありますか？
それとテーブルのサンプルは画像の他にテキストでもつけてください

[MagMag]

ありがとうございます。(uid,key)はユニークです。イメージとして、uidが料理名、keyが材料名、valueが使用量です。テキスト化も実施しました。ちなみに、図でも意図が伝わると思ったので、図で掲載としたのですが、テキストで表示することの、回答される方に対するメリットってどこにあるのでしょうか？

Answer 1

キーが増える度にクエリも書き換えなければならず、不便だと感じています。

取得側で列を展開する必要がありますが、以下の様にするとSQLは変更が不要です。

select uid
     , group_concat(key order by key) key_list 
     , group_concat(max_value order by key) value_list
from ( 
  SELECT uid, key, max(value) max_value 
  FROM vtable
  GROUP BY uid, key 
) t
group by uid

解説：key_list のkeyの並びとvalue_listの並びが一致するようになっています。
※文字列への変換は考慮していませんので、必要に応じて適宜変更して下さい。

Comments

[MagMag]

ありがとうございます！

Answer 2

定形文をプロシージャで処理すればよいでしょう
列順の指定などこまかい仕様を決める必要はありそうですけど。
また表示するのが数値で確定ですか？文字列と数値だと若干処理がかわります

Comments

[MagMag]

ありがとうございます。実データの場合、uidもkeyもテキストですが、uidのINTへの変換は性質上可能です。

[yambejp]

ちなみにkeyというカラム名は予約語と競合するのでおすすめしません
仮にkとしついでにvalueをvとしておきます
create table vtable(uid int,k varchar(10),v int,unique(uid,k));

insert into vtable values
(101,'c1',11),
(101,'c2',12),
(101,'c3',13),
(102,'c1',21),
(102,'c2',22),
(102,'c3',23);

[yambejp]

これをさらっとかくとこう
select
uid
,sum((c1=k)*v) as c1
,sum((c2=k)*v) as c2
,sum((c3=k)*v) as c3
from vtable
,(select 'c1','c2','c3') as sub
group by uid

この流れをプロシージャにするとよいです

[yambejp]

drop procedure if exists proc;
delimiter //
create procedure proc()
begin
declare c varchar(10);
declare done int default 0;
declare cur cursor for
select distinct k from vtable order by k;
declare continue handler for sqlstate '02000' set done=1;
set @sql1:="select uid";
set @sql2:="";
open cur;
repeat
fetch cur into c;
if not done then
set @sql1:=concat(@sql1,',sum((`',c,'`=k)*v) as `',c,'`');
set @sql2:=concat(@sql2,case @sql2 when '' then 'select ' else ',' end, '"',c,'"');
end if;
until done end repeat;
close cur;
set @sql1:=concat(@sql1,'from vtable,(',@sql2,') as sub group by uid');
prepare stmt from @sql1;
execute stmt;
end;

[yambejp]

//実行
call proc();

※微調整をいくつかいれました
あとはvtableのデータを色々いじって試してみてください

[MagMag]

ありがとうございます。プロシージャを勉強しながら実装していきたいと思います。アプリケーション側での対応不要というもともとの目的に照らし、yambejpさんをベストアンサーとさせてください。

[sazi]

> アプリケーション側での対応不要

カラムが動的に変化しても、アプリケーション側の変更はは不要なんですね。
テーブルのレイアウト変えるのも問題ないって事ですか。

[MagMag]

この例ではアプリケーション側で機械学習に一括でかけるので、カラムが増えても追加処理は不要です。
一方、データ抽出においてはこのデータだけでなく、他の条件で抽出したデータとjoinしないといけないため、抽出はSQLだけで済ませたいと思っていました。イメージが伝わりきらず申し訳ありません。

saziさんの方法もシンプルで大変わかりやすいので、ベストアンサーに選ぼうか迷ったのですが、、。

[sazi]

問題ないなら、スルーしてOKです。