时间戳

首页 > 解决方案 > SQL 2014 等效于 string_agg 返回整数值

sql-server - SQL 2014 等效于 string_agg 返回整数值

问题描述

不幸的是，该string_agg函数直到 SQL 2017 才出现（我使用的是 2014），所以我使用XML PATH. 它正在处理一个（致命的）缺陷——它每次都省略第RoleID一个——第一个可见角色总是以逗号开头。

背景：我有一个表，其中安全角色存储为整数值，每行一个安全角色（称为 RoleID）。列的顺序是：UserRoleID、UserID、RoleID 等（如下表所示）。我不需要 UserRoleID，只需要 UserID 后跟以逗号分隔的 RoleID 列表。

例如，UserID 14 具有以下角色：1、2、5、6、7 和 8 在 UserRoles 表中作为单独的行。我创建的 XML 路径（抄袭 - 我不是那么好）返回以下内容——您会注意到缺少角色 1，而第二个角色 (2) 前面有一个逗号。结果应该是 1,2,5,6,7,8

结果：

UserID  RoleID

14      ,2,5,6,7,8

这是SQL代码：

SELECT UserID, RoleID = STUFF((SELECT ',' + cast(UR2.RoleID as varchar(max) )
  FROM dbo.UserRoles AS UR2
   WHERE UR2.UserID = UR.UserID 
   ORDER BY RoleID
   FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.UserRoles AS UR
WHERE UserID=14
GROUP BY UserID

这是 UserID 14 的原始表值：

UserRoleID  UserID  RoleID  ExpiryDate  IsTrialUsed EffectiveDate   CreatedByUserID CreatedOnDate   LastModifiedByUserID    LastModifiedOnDate  Status  IsOwner
37  14  1   NULL    1   NULL    1   2020-05-20 21:55:07.387 1   2020-05-20 21:55:07.387 1   0
38  14  2   NULL    1   NULL    1   2020-05-20 21:55:07.390 1   2020-05-20 21:55:07.390 1   0
39  14  5   2020-06-30 00:00:00.000 1   NULL    1   2020-05-20 21:55:07.400 1   2020-05-20 21:55:07.400 1   0
40  14  6   2021-01-01 00:00:00.000 1   2020-05-18 00:00:00.000 1   2020-05-20 21:55:07.403 1   2020-05-20 21:55:07.403 1   0
41  14  7   NULL    1   NULL    1   2020-05-20 21:55:07.410 1   2020-05-20 21:55:07.410 1   0
51  14  8   NULL    1   NULL    1   2020-06-12 16:14:25.640 1   2020-06-12 16:14:25.640 1   0

关于如何让第一个角色按预期显示所选用户 ID 的任何建议？

标签： sql-serverfor-xml-path

解决方案

推荐阅读