MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table data (col1 varchar(100), col2 varchar(100), col3 varchar(100));
insert into data values('1','gold_room','1,2,3');
insert into data values('2','silver_room','1,2,3');
insert into data values('3','brown_room','2,4,6');
insert into data values('4','brown_room','3');
insert into data values('5','gold_room','4,5,6');
select * from data;


col1
col2
col3


1
gold_room
1,2,3

2
silver_room
1,2,3

3
brown_room
2,4,6

4
brown_room
3

5
gold_room
4,5,6


select col2, GROUP_CONCAT(col3) from data group by col2;


col2
GROUP_CONCAT(col3)


brown_room
2,4,6,3

gold_room
1,2,3,4,5,6

silver_room
1,2,3

col1	col2	col3
1	gold_room	1,2,3
2	silver_room	1,2,3
3	brown_room	2,4,6
4	brown_room	3
5	gold_room	4,5,6