如何将值分隔成行

Question

我有这样的数据:

<表类> ID 角色 控制单元 用户ID tbody><<tr>00196SPV/TL CS00 g (nol)0016506, 0016551, 001654100237SPV, CS, TL14我1458506, 145854100196SPV/CS/TL0 c30435506, 0435541, 0435551

请尝试以下解决方案。

它正在使用JSON标记标记字符串。

/p>

-- DDL and sample data population, start
DECLARE @tbl table (ID CHAR(5), ROLE VARCHAR(200), ControlUnit VARCHAR(20), UserID VARCHAR(200))
INSERT @tbl (ID, Role, ControlUnit, UserID) VALUES
('0019', 'SPV / TL / CS', '00G (nol)',  '0016506 , 0016551 , 0016541'),
('0023', 'SPV / CS / TL', '14I',        '1458506, 1458541, 0435551'),
('0019', 'SPV / CS / TL', '0C3',        '0435506, 0435541, 0435551');
-- DDL and sample data population, end
;WITH rs AS 
(
SELECT ID, ControlUnit, Role, UserID
, s = '["' + REPLACE(STRING_ESCAPE([Role],'json'), '/', '","') + '"]'
, s1 = '["' + REPLACE(STRING_ESCAPE(UserID,'json'), ',', '","') + '"]'
FROM @tbl
)
SELECT ID, TRIM([Role].[value]) AS [Role], ControlUnit, TRIM(UserID.[value]) AS UserID
FROM rs
CROSS APPLY OPENJSON (s, N'$') AS [Role]
CROSS APPLY OPENJSON (s1, N'$') AS UserID
WHERE [Role].[key] = UserID.[key];

<表类>ID角色ControlUnitUserIDtbody><<tr>0019SPV00 g (nol)00165060019TL00 g (nol)00165510019c00 g (nol)00165410023SPV14我14585060023c14我14585410023TL14我04355510019SPV0 c304355060019c0 c304355410019TL0 c30435551

可以使用STRING_SPLIT()函数，只要确保你的分隔符规范化-

• 决定一种类型和样式的分隔符，例如逗号
• 确保在DB中始终只使用此分隔符
• 或者您可以在查询中使用REPLACE()函数来修复它

那么你可以像这样使用STRING_SPLIT()函数:

SELECT  T.ID                AS  ID,
Roles.RoleName      AS  [Role],
T.[Control Unit]    AS  [Control Unit],
UserIds.UserId      AS  [User ID]
FROM    UsersToRoles        AS  T
OUTER APPLY
(
SELECT  S.ordinal       AS  RoleOrder,
S.[value]       AS  RoleName
FROM    STRING_SPLIT(T.[Role], ',') AS  S
)   AS  Roles
OUTER APPLY
(
SELECT  S.ordinal       AS  UserIdOrder,
S.[value]       AS  UserId
FROM    STRING_SPLIT(T.[User ID], ',')  AS  S
)   AS  UserIds
WHERE   UserIds.UserIdOrder =   Roles.RoleOrder