Hi,
I have the below script and displays the results in multiple rows.
I would like to combine the multiple column values (groups & users) in to single row.
Could anyone please help on this.
SELECT
d.Code AS Code,
prj.name as [PName],
grp.groupid as Groups,
usr.userid as Users
FROM MASTER d
INNER JOIN ACCESS acc on acc.num = d.num
LEFT OUTER JOIN GROUPS grp on acc.GP_ID = grp.groupnum
LEFT OUTER JOIN USERS usr on acc.GP_ID = usr.usernum
LEFT OUTER JOIN PROJECTS prj on d.num = prj.num
WHERE
d.Code in ('50000')
ORDER BY grp.groupid,prj.name
E.g:
Current result
|Code|PName|Groups|Users|
|50000|Cor 2020|NULL|Usr1|
|50000|Cor 2020|NULL|Usr2|
|50000|Cor 2020|NULL|Usr3|
|50000|Cor 2020|Grp1||
|50000|Cor 2020|Grp2||
|50000|Cor 2020|Grp3||
|Expected result|
|Code|PName|Groups|Users|
|50000|Cor 2020|Grp1,Grp2,Grp3|Usr1,Usr2,Usr3|
Thanks in advance.