How to concatenate multiple rows in flexibleSearch query in Hybris

Question

My query is returning multiple rows for each group that the user is assigned to, example below:

I need to concatenate each group on a single row for each user, like: groupA, groupB, groupC

I tried using SUBSTRING, SUB SELECT, GROUP_CONCAT.. Nothing worked so far.

Here is the query that returned the example image

SELECT DISTINCT { customer.uid } AS email,
{ customer.lastLogin } AS lastLogin,
{ customer.company } AS company,  
{ unit.topLevel } AS site,
{group.uid} AS groups

FROM { B2BCustomer AS customer 
JOIN CustomerCMSSiteRelation AS site 
ON { site.source } = { customer.PK } 
JOIN B2BUnit AS unit 
ON {customer.defaultB2BUnit} = {unit.pk}
JOIN PrincipalGroupRelation AS rel ON {customer:PK} = {rel:source}
JOIN UserGroup AS group ON {rel:target} = {group:PK} }

WHERE {unit.topLevel} = 'AR'

Answer 1

• if string_agg() doesn't work replace it with group_concat

with main as (

SELECT DISTINCT { customer.uid } AS email,
{ customer.lastLogin } AS lastLogin,
{ customer.company } AS company,  
{ unit.topLevel } AS site,
{group.uid} AS groups

FROM { B2BCustomer AS customer 
JOIN CustomerCMSSiteRelation AS site 
ON { site.source } = { customer.PK } 
JOIN B2BUnit AS unit 
ON {customer.defaultB2BUnit} = {unit.pk}
JOIN PrincipalGroupRelation AS rel ON {customer:PK} = {rel:source}
JOIN UserGroup AS group ON {rel:target} = {group:PK} }

WHERE {unit.topLevel} = 'AR'
)

select 
email,
lastLogin,
company,
site,
STRING_AGG(groups,',') as groups_combined
from main
group by 1,2,3,4