SQLTeam.com | Weblogs | Forums

Combine and display multiple column values in to single row

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.

hi

hope this link helps