how to get comma separated value using aggregate?
Ranjeet Singh you can try this SQL:
SELECT {User}.[Name],STUFF ( ( SELECT ',' + r.Name FROM {Role} r inner join {UserRole} on {UserRole}.[Role_Id] = r.[Id] inner join {User} u on u.[id] = {UserRole}.[User_Id] where u.[id] = {User}.[Id] ORDER BY r.Name FOR XML PATH('') ), 1, 1, '' ) AS Roles from {User}
LuÃs Cardoso wrote:
Hi Ranjeet,
As per given solution by LuÃs Cardoso is working fine. Just add on to your query, Add distinct keyword with select and do also join with Role and UserRole table outside the STUFF column so after that those User will be filter who has not any role in application.
Jitender Gaur wrote:
Hello Jitendra
Thank you so much for your valuable suggestion.
Hello LuÃs Cardoso
Thank you so much for your valuable answer.
Ranjeet Singh wrote:
Easiest solution would be go with SQL and use the 'COALESCE ' function to have comma separate values within SQL Query
Hi,
You need to add a new atrribute (last column on fields list):
On the Value of the Attribute you can select the fields and add the comma:
Please see this example:
Hope it helps.
PS: If you want to do like this example you don't need to change to SQL
BR,
LuÃs
Thank you for answer but for my case user can have multiple roles than how can i show multiple roles in single column.
It depends on what you want. Can you give an example of the desired output? Why do you need it comma-seperated?
Kilian Hekhuis wrote:
Hi @Kilian Hekhuis
Output should be like :
UserName Roles
mary jane HR,Manager
That's something that's not solvable by a query alone, as far as my knowledge goes. If you want to show output on the screen, you could use a Web Block that has the user Id as input, that performs a query and concatenates the roles (e.g. by using String_Join from the Text Extension). But that will of course increase the number of queries. Alternatively, you could use your Aggregate and then "hand build" the desired output by looping over the results and create a new List on the fly.
You can do a SQL to get the Roles separeted by comma with this statment:
SELECT STUFF
(
SELECT ',' + r.Name
FROM {Role} r
ORDER BY r.Name FOR XML PATH('')
),
1, 1, ''
) AS Roles
I fully agree with Luis Cardoso's solution.
However, if you want to bypass the complex SQL, then
Get the aggregate of roles list
And in your action perform a string_join passing the list as parameter and comma (,) as separator
Regards,
Saugat