How to separate values ​into rows

I have data like this:

I want to separate the User ID based on their respective roles, so it looks like this:

The rules of User IDs is:

• XXXX506 - XXXX510 = SPV
• XXXX541 - XXXX550 = CS
• XXXX551 - XXXX560 = TL

The first four digits of each User ID are the branch code, so you can ignore that. Just take the last three numbers for each role.

Please try the following solution.

It is using JSON to tokenize string of tokens.

SQL

-- 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];

Output

You can use STRING_SPLIT() function,
just make sure to normalize your separators-

• decide on one type and style of separator, for example comma
• either make sure in DB to always use only this separator
• or you can use REPLACE() functions in your query to fix it

Then you can use the STRING_SPLIT() function like this:

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