Need to convert the rows into a Column

Transact-SQL
1

Hi,

I have a data like below,

USR MAIL ID ROLE ACCESS
John john@gmail.com Mgr US
John john@gmail.com Mgr Europe
John john@gmail.com Mgr Asia
Tony Tony@gmail.com Mgr Asia

I want the data to be displayed like

USR MAIL ID ROLE ACCESS
John john@gmail.com Mgr US,Europe,Asia
Tony Tony@gmail.com Mgr Asia

Can you please let me know how to achieve this through the sql query

2

By using For XML Path

select Mail, stuff(( select distinct ',' + a.Role
from tbl a
where a.mail = b.mail
for xml path('') ),1,1,'') as roles
from tbl b

3

Try this

--drop table test

create table test (
[USR] varchar(50),
[MAIL ID] varchar(50),
[ROLE] varchar(50),
[ACCESS] varchar(50))

insert into test
select 'John' as [USR] , 'john@gmail.com' as [MAIL ID], 'Mgr' as [ROLE], 'US' as [ACCESS]
union all
select 'John' as [USR] , 'john@gmail.com' as [MAIL ID], 'Mgr' as [ROLE], 'Europe' as [ACCESS]
union all
select 'John' as [USR] , 'john@gmail.com' as [MAIL ID], 'Mgr' as [ROLE], 'Asia' as [ACCESS]
union all
select 'Tony' as [USR] , 'Tony@gmail.com' as [MAIL ID], 'Mgr' as [ROLE], 'Asia' as [ACCESS]

--Original
select * from test

--Pivot
select
[USR]
,[MAIL ID]
,[ROLE]
,[US]
,[Europe]
,[Asia]
from(
select * from test ) as P pivot (

max(access) for access in (

[US]
,[Europe]
,[Asia]
))pt