SQL Server 2016

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

create table cte
(
  ID int,
  name nvarchar(10),
  email nvarchar(20),
  role nvarchar(10),
  campus nvarchar(10)
)

insert into cte (ID, name, email, role, campus) values
(1, 'User1', 'user@gmail.com',  'Admin',     'Egypt'  ),
(2, 'User1', 'user@gmail.com',  'Moderator', 'Egypt'  ),
(3, 'User1', 'user@gmail.com',  'Guest',     'USA'    ),
(4, 'User2', 'user2@gmail.com', 'User',      'Brazil' ),
(5, 'User2', 'user2@gmail.com', 'Admin',     'Cairo'  ),
(6, 'User2', 'user2@gmail.com', 'Dummy',     'Namibia');
6 rows affected

--OP attempt

select id,
       name,
       stuff((select email + ',' from cte FOR XML PATH ('')), 1, 1, '')
from cte
group by id, name

/*
ID     name      email              roles                      campus
1      User1     user@gmail.com     Admin,Moderator,Egypt      Egypt,Egypt,USA
2      User2     user1@gmail.com    User,Admin,Dummy           Brazil,Cairo,Namibia 
*/


id
name
(No column name)


1
User1
ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,

2
User1
ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,

3
User1
ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,

4
User2
ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,

5
User2
ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,

6
User2
ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,


select row_number() over(order by c1.name) as ID,
       c1.name,
       c1.email,
       stuff((  SELECT ',' + Role
                FROM cte c3
                WHERE c3.name = c1.name
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS Roles,
       stuff((  SELECT ',' + Campus
                FROM cte c3
                WHERE c3.name = c1.name
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS Campuses
from cte c1
group by c1.name, c1.email
order by c1.name;


ID
name
email
Roles
Campuses


1
User1
user@gmail.com
Admin,Moderator,Guest
Egypt,Egypt,USA

2
User2
user2@gmail.com
User,Admin,Dummy
Brazil,Cairo,Namibia

id	name	(No column name)
1	User1	ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,
2	User1	ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,
3	User1	ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,
4	User2	ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,
5	User2	ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,
6	User2	ser@gmail.com,user@gmail.com,user@gmail.com,user2@gmail.com,user2@gmail.com,user2@gmail.com,

ID	name	email	Roles	Campuses
1	User1	user@gmail.com	Admin,Moderator,Guest	Egypt,Egypt,USA
2	User2	user2@gmail.com	User,Admin,Dummy	Brazil,Cairo,Namibia