sql - How to concate multiple row using STUFF and for XML PATH in multiple table -

sql - How to concate multiple row using STUFF and for XML PATH in multiple table -

i have 3 tables.

dbo_users dbo_locations dbo_user_locations

i want location name location table. multiple location has been assigned users. want location in 1 row against username

here query ran

select u.id, u.username, lo.location_id, loc.name dbo_users u inner bring together dbo_user_locations lo on u.id = lo.user_id inner bring together dbo_locations loc on loc.id = lo.location_id order 1, 2

and output

id username location_id name ----------------------------------------------------- 5 admin 26 avis pharmacy thorpes 6 james 1 welches 12 khalliday 16 white park road (life) 12 khalliday 32 daewoods mall lower swan st 12 khalliday 19 cellular planet black stone 12 khalliday 18 cellular planet f street 12 khalliday 41 digicel centre manor lodge 12 khalliday 20 digicentre swan street 12 khalliday 21 cellular planet oistins 25 teller2 18 cellular planet f street 28 tforde 16 white park road (life) 157 tushar 21 cellular planet oistins 157 tushar 19 cellular planet black stone 157 tushar 34 mall internationale 157 tushar 16 white park road (life) 165 asdc 19 cellular planet black stone 170 ate 16 white park road (life) 172 smalution1 16 white park road (life) 173 kh 20 digicentre swan street

i need output this

12 khalliday white park road (life),daewoods mall lower swan st, cellular planet black rock, cellular planet f street, digicel centre manor lodge, digicentre swan street, cellular planet oistins

means location corresponding 12 id come in front end of id.

i trying query getting error on xml path

select distinct u.id, u.username, stuff( (select ', '+ l.name dbo_locations l inner bring together dbo_users_locations ul on ul.location_id = l.id ul.user_id = u.id xml path('')), 1, 1, '') locationname dbo_users u;

i appreciate help!

thanks in advance.

sql