mysqlのgroup_concat

mysqlで、行毎の文字列データを連結するには、group_concat を使います。

■テーブル定義
drop table if exists id;
create table id (
id varchar(255)
);

insert into id set id = 'id1';
insert into id set id = 'id2';

drop table if exists score;
create table score (
id varchar(255),
subject varchar(16),
score int
);

insert into score set id = 'id1', subject = 'math', score = '90';
insert into score set id = 'id1', subject = 'sci', score = '80';
insert into score set id = 'id1', subject = 'eng', score = '70';
insert into score set id = 'id2', subject = 'math', score = '100';

■select文
select
i.id as id
, group_concat(s.subject, ',', s.score order by subject separator ',') \
as scores
from
id i
, score s
where
i.id = s.id
group by
i.id
order by
i.id
;

■検索の実行結果
id scores
id1 eng,70,math,90,sci,80
id2 math,100