sql—将相同id的行数据转换为同一行数据时用逗号分隔

e37o9pze 于 2021-07-24 发布在 Java

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

我需要帮助关于我所附的截图。
场景：一个用户可以有多个角色。所以在屏幕截图中，相同的userid有不同的rowid和rolename。我想把它们显示在一行中，就像我在屏幕截图中所显示的那样。

SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Id, 
    U.UserId, U.FirstName, U.LastName, 
    (U.FirstName + ' ' + U.LastName) AS FullName, 
    u.email, u.PhoneNumber, u.Username,
    COALESCE(RR.RoleId, PU.RoleId, sbr.RoleId) AS RoleId,
    COALESCE(RR.RoleName, R.RoleName,sbr.RoleName) AS RoleName,
    COALESCE(RR.RoleType, R.RoleType,sbr.RoleType) AS RoleType, u.[Image]
FROM
    [User] U
LEFT JOIN
    PlatformUser PU ON PU.UserId = u.UserId AND PU.IsDeleted <> 1
LEFT JOIN
    Role R ON R.RoleId = PU.RoleId 
LEFT JOIN
    UserToSchool UTS ON UTS.UserId = u.UserId AND UTS.IsDeleted <> 1
LEFT JOIN
    Role RR ON RR.RoleId = UTS.RoleId
LEFT JOIN 
    dbo.UserToSchoolBranch usb ON usb.UserId = u.UserId AND usb.IsDeleted <> 1
LEFT JOIN  
    dbo.Role sbr ON sbr.RoleId = usb.RoleId
WHERE
    (RR.RoleType = @pRoleType OR R.RoleType = @pRoleType OR sbr.RoleType = @pRoleType) 
    AND ISNULL(u.isdeleted, 0) <> 1

sql sql-server Database select

来源：https://stackoverflow.com/questions/63316977/comma-separated-when-same-id-rows-data-into-in-one-row-data

1条答案

按热度按时间

xghobddn1#

如果您的查询为cte，那么您可以使用 FOR XML PATH() 要获得一行：

with cte as
(
    your query
)
select distinct UserId, FirstName, LastName, FullName, email, PhoneNumber, Username, 

    stuff((select Concat(',',RoleId) 
    from cte cte1
    where cte1.UserId = cte2.UserId 
    for xml path('')),1,1,'') All_roles_Id,

    stuff((select Concat(',',RoleName) 
    from cte cte1
    where cte1.UserId = cte2.UserId 
    for xml path('')),1,1,'') All_roles_name

from cte cte2

或者你可以试着用 String_AGG （sql server 2017）：

select UserId, FirstName, LastName, FullName, email, PhoneNumber, Username, 
   string_agg(RoleId,',') All_roles_Id,
   string_agg(RoleName,',') All_roles_Name
   from cte
   group by UserId, FirstName, LastName, FullName, email, PhoneNumber, Username

赞(0）回复(0）举报 2021-07-24

我来回答

sql—将相同id的行数据转换为同一行数据时用逗号分隔

1条答案

相关问题

热门标签

最新问答