[SQL] 縦持ちのテーブルから横持ちのデータとして検索する

Question

DBのデータを画像のように並べ替えをして取得したいですがどのようにしたら良いでしょうか？？

Comments

[m.ts10806]

MySQL　Oracleとタグに付けられていますが、結局どのDBを想定されていますでしょうか？
またテーブルのCREATE TABLE文とサンプルデータのINSERT文をご提示ください。
そのうえで、自身が試したこと調べたことを記載してください

[wada3]

失礼しました。
mysql対象になります。

[m.ts10806]

質問本文とタグを調整してください

[m.ts10806]

テーブルのCREATE TABLE文とサンプルデータのINSERT文をご提示ください。
そのうえで、自身が試したこと調べたことを記載してください

Answer 1

普通にやると同じidに順位が付かないのでどちらがname1/name2なのか確定できないので
命題のような処理は不可能です。

sample

無理やりやるならこんな感じ

• 元データ

create table tbl(
pid int primary key,
id int,
name varchar(10),
price int,
bikou varchar(30)
);
insert into tbl values
(1,1,'あか',100,'aa'),
(2,1,'あお',200,'bb'),
(3,2,'きいろ',300,'aa'),
(4,2,'みどり',400,'bb'),
(5,3,'おれんじ',500,'aa');

• 抽出

select 
t2.name as name1,
t2.price as price1,
t2.bikou as bikou1,
t4.name as name2,
t4.price as price2,
t4.bikou as bikou2
from (select *
from tbl as t1
where (select count(*)+1 from tbl where id=t1.id and pid<t1.pid)=1) as t2
left join  (select *
from tbl as t3
where (select count(*)+1 from tbl where id=t3.id and pid<t3.pid)=2) as t4
on t2.id=t4.id

これを右方向に自動的に伸ばしていきたいならプロシージャが必要になります。

右方向にどんどん伸ばす

• 元データ

create table tbl(
pid int primary key,
id int,
name varchar(10),
price int,
bikou varchar(30)
);

insert into tbl values
(1,1,'あか',100,'aa'),
(2,1,'あお',200,'bb'),
(3,2,'きいろ',300,'aa'),
(4,2,'みどり',400,'bb'),
(5,3,'おれんじ',500,'aa'),
(6,1,'むらさき',600,'xx'),
(7,1,'みずいろ',700,'yy'),
(8,2,'ちゃいろ',800,'zz');

• procedure作成

drop procedure if exists myproc;
delimiter //
create procedure myproc()
begin
select max(cnt) into @max from (
select id,count(*) cnt from tbl group by id
) as sub;
set @sql="select ";
set @c=1;
while @c<=@max do
if @c>1 then
set @sql=concat(@sql,",");
end if;
set @sql=concat(@sql,"t",@c,".name as name",@c,",t",@c,".price as price",@c,",t",@c,".bikou as bikou",@c);
set @c=@c+1;
end while;
set @c=1;
while @c<=@max do
if @c=1 then
set @sql=concat(@sql," from");
else
set @sql=concat(@sql," left join");
end if;
set @sql=concat(@sql,"(select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=",@c,") as t",@c);
if @c>1 then
set @sql=concat(@sql," on t1.id=t",@c,".id");
end if;
set @c=@c+1;
end while;
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;

※procedureは一回作成すればあとはデータが変化してもcallするだけでよいです

• procedure実行

call myproc;

実際にprocedureが発行しているSQL文

上記procedureが発行しているSQL文はこうです

select 
t1.name as name1,
t1.price as price1,
t1.bikou as bikou1,
t2.name as name1,
t2.price as price1,
t2.bikou as bikou1,
t3.name as name1,
t3.price as price1,
t3.bikou as bikou1,
t4.name as name2,
t4.price as price2,
t4.bikou as bikou2
from 
(select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=1) as t1
left join
(select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=2) as t2
on t1.id=t2.id
left join
(select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=3) as t3
on t1.id=t3.id
left join
(select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=4) as t4
on t1.id=t4.id

結果

name1	price1	bikou1	name2	price2	bikou2	name3	price3	bikou3	name4	price4	bikou4
あか	100	aa	あお	200	bb	むらさき	600	xx	みずいろ	700	yy
きいろ	300	aa	みどり	400	bb	ちゃいろ	800	zz	NULL	NULL	NULL
おれんじ	500	aa	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Comments

[wada3]

連番を画像のように追加しましたがどうでしょうか

[yambejp]

sampleつけておきました

[wada3]

ありがとうございます。名前3を追加したい場合どのようにすればよいでしょうか

[yambejp]

> 名前3を追加したい場合

書いてあるとおりプロシージャで処理するのが妥当です。
プロシージャのサンプルもつけておきました

[wada3]

sqlだけでは難しいでしょうか

[yambejp]

> sqlだけでは難しいでしょうか

これはSQLだけです。（プロシージャはSQLの機能です）
SQL文だけでやる場合右方向にどのくらい伸ばしていいかは
検索してみないとわからないので、自動的に右に伸ばすことは不可能です

[wada3]

workbenchでも可能でしょうか

[yambejp]

> workbenchでも可能でしょうか

しりません
実際にやって動けばできるし、動かなければできないでしょう。
逆に実際に試さずに質問しているのですか？

解答欄に「実際にprocedureが発行しているSQL文」という処理を記載しておきました
どうしてもベタなSQL文だけでやるなら、列数が変わるたびに例示のような拡張を
手動でやってください

Answer 2

MySQLでは GROUP_CONCAT
Oracle では LISTAGG
でググってください。