How to get comma separated string using calculated column in Aggregate?

how to get comma separated value using aggregate?

Ranjeet Singh wrote:

how to get comma separated value using aggregate?

Hi Ranjeet,

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

Hi Ranjeet,

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 Ranjeet,

It depends on what you want. Can you give an example of the desired output? Why do you need it comma-seperated?

Hi @Kilian Hekhuis

Output should be like :

UserName          Roles

mary jane            HR,Manager




Luís Cardoso wrote:

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.


Hi Ranjeet,

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

Solution

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}
Solution

Luís Cardoso wrote:

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}


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:

Luís Cardoso wrote:

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}


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.   

 

Hello Jitendra

Thank you so much for your valuable suggestion.


Luís Cardoso wrote:

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}

Hello Luís Cardoso

Thank you so much for your valuable answer.


Hi Ranjeet,


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