By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Table1(UserID int, Type varchar(255), ProductID varchar(255));
INSERT dbo.Table1(UserID, Type, ProductID)
VALUES(1, 'A, B', '001, 003');
SELECT t.UserID, [Type] = LTRIM(j1.value), Product = LTRIM(j2.value)
FROM dbo.Table1 AS t
CROSS APPLY OPENJSON(CONCAT('["',REPLACE(Type,',','","'),'"]')) AS j1
CROSS APPLY OPENJSON(CONCAT('["',REPLACE(ProductID,',','","'),'"]')) AS j2
WHERE j1.[key] = j2.[key];
| UserID | Type | Product |
|---|---|---|
| 1 | A | 001 |
| 1 | B | 003 |