MariaDB 10.6

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

CREATE TABLE intercom_teammates (
TeammateID INT ,
Teammatename varchar(15)  , 
Teamid int,
Teams varchar(25)  );

insert into intercom_teammates values 
(1,'Amy',11,'Sales'),
(1,'Amy',12,'Support'),
(1,'Amy',13,'Marketing'),
(2,'Peter',12,'Support'),
(2,'Peter',13,'Marketing');

Records: 5  Duplicates: 0  Warnings: 0


select *
from intercom_teammates
 ;


TeammateID
Teammatename
Teamid
Teams


1
Amy
11
Sales

1
Amy
12
Support

1
Amy
13
Marketing

2
Peter
12
Support

2
Peter
13
Marketing


select it.TeammateID,
       group_concat(distinct it.Teammatename SEPARATOR ',') as Teammatename,
       group_concat( it.Teamid SEPARATOR ',') as Teamid,
       group_concat( it.Teams SEPARATOR ',') as Teams
from intercom_teammates it
group by it.TeammateID ;
         


TeammateID
Teammatename
Teamid
Teams


1
Amy
11,12,13
Sales,Support,Marketing

2
Peter
12,13
Support,Marketing

TeammateID	Teammatename	Teamid	Teams
1	Amy	11	Sales
1	Amy	12	Support
1	Amy	13	Marketing
2	Peter	12	Support
2	Peter	13	Marketing

TeammateID	Teammatename	Teamid	Teams
1	Amy	11,12,13	Sales,Support,Marketing
2	Peter	12,13	Support,Marketing