Context I want to retrieve data about accounts and their associated user roles. Since we are using a low code framework the relations are kept in a separate table as well (association of account and user role, besides account and user role table). We use MS SQL.
Current situation: When I do the following query I do get the users and roles, but they are on seperate lines. I want to concatenate the roles of same user into same value :
select us.name, * from administration$account a
inner join system$userroles uss on a.id = uss.system$userid
inner join system$userrole us on us.id=uss.system$userroleid
Results in
Role id name system&userid system$userroleid id
LocationManager 41939771529887748 Britt 41939771529887748 4785074604081160 4785074604081160
PlacementManager 41939771529887748 Britt 41939771529887748 4785074604081353 4785074604081353
Required situation: This should be merged like
Role Name
LocationManager,PlacementManager Britt
Question: How should the query be with respecting the inner joins per account, so that you see per all the user roles of that account in one row?
