SQL Server 如何在Hybris中的flexibleSearch查询中连接多行

nfs0ujit 于 2022-11-21 发布在其他

关注(0)|答案(1)|浏览(138)

我的查询为用户分配到的每个组返回多行，示例如下：

我需要将每个用户的每个组连接到一行，如下所示：A组、B组、C组
我试过使用SUBSTRING、SUB SELECT、GROUP_CONCAT..到目前为止没有任何效果。
下面是返回示例图像的查询

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'

sql-server

来源：https://stackoverflow.com/questions/74363108/how-to-concatenate-multiple-rows-in-flexiblesearch-query-in-hybris

1条答案

按热度按时间

yduiuuwa1#

如果string_agg()不起作用，请将其替换为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

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 如何在Hybris中的flexibleSearch查询中连接多行

1条答案

相关问题

热门标签

最新问答