グループ内の結果セットを単一列に連結する

 

• MySQL

(8.4.0)

drop table tab1;
create table tab1(
 col1  int,
 col2 int,
 col3 varchar(10)
);

 

insert into tab1 values (1, 10, 'A');
insert into tab1 values (1, 20, 'B');
insert into tab1 values (2, 10, 'C');
insert into tab1 values (2, 20, 'D');
insert into tab1 values (2, 30, 'E');
insert into tab1 values (3, 10, 'F');

select * from tab1;

select col1, group_concat(col3 order by col2 separator ',' ) 
from tab1
group by col1
order by col1
;

 

• Oracle

(23c)

drop table tab1 purge;
create table tab1(
 col1  int,
 col2 int,
 col3 varchar2(10)
);

insert into tab1 values (1, 10, 'A');
insert into tab1 values (1, 20, 'B');
insert into tab1 values (2, 10, 'C');
insert into tab1 values (2, 20, 'D');
insert into tab1 values (2, 30, 'E');
insert into tab1 values (3, 10, 'F');

commit;

select * from tab1;

select col1, listagg(col3, ',') within group (order by col2) 
from tab1
group by col1
order by col1
;

 

• PostgreSQL

 

(17)

drop table tab1;
create table tab1(
 col1  int,
 col2 int,
 col3 varchar(10)
);

insert into tab1 values (1, 10, 'A');
insert into tab1 values (1, 20, 'B');
insert into tab1 values (2, 10, 'C');
insert into tab1 values (2, 20, 'D');
insert into tab1 values (2, 30, 'E');
insert into tab1 values (3, 10, 'F');

select * from tab1;

select col1, array_to_string(array_agg(col3 order by col2), ',') 
from tab1 
group by col1
order by col1
;

 

• SQL Server

(2022)

drop table tab1;
create table tab1(
 col1  int,
 col2 int,
 col3 varchar(10)
);

insert into tab1 values (1, 10, 'A');
insert into tab1 values (1, 20, 'B');
insert into tab1 values (2, 10, 'C');
insert into tab1 values (2, 20, 'D');
insert into tab1 values (2, 30, 'E');
insert into tab1 values (3, 10, 'F');

select * from tab1;

select col1, string_agg(col3, ',') within group (order by col2) 
from tab1
group by col1
order by col1
;