Split multiple strings in SQL

I am trying to split multiple columns of strings at the same time.

My original data looks like:

Table1

UserID	Type	ProductID
1	A, B	001, 003

and I want to end up with

UserID	Type	ProductID
1	A	001
1	B	003

When I use

select *
from Table1
cross apply string_split(Type, ',')
cross apply string_split(ProductID, ',')

I end up with this table that I do not want…

UserID	Type	ProductID
1	A	001
1	B	003
1	A	003
1	B	001

How do I split multiple columns of strings simultaneously?

Answer

In SQL Server 2016 and above, you can use OPENJSON to split strings with deterministic ordering. Given this sample data:

CREATE TABLE dbo.Table1
(
   UserID int, 
   Type varchar(255), 
   ProductID varchar(255)
);

INSERT dbo.Table1(UserID, Type, ProductID)
VALUES(1, 'A, B', '001, 003');

You can use this query:

SELECT t.UserID, [Type] = LTRIM(j1.value), Product = LTRIM(j2.value)
FROM dbo.Table1 AS t
CROSS APPLY OPENJSON
  (CONCAT('["',REPLACE(STRING_ESCAPE(Type, 'json'),',','","'),'"]')
) AS j1
CROSS APPLY OPENJSON
  (CONCAT('["',REPLACE(STRING_ESCAPE(ProductID, 'json'),',','","'),'"]')
) AS j2
WHERE j1.[key] = j2.[key];

Which produces this output:

UserID	Type	Product
1	A	001
1	B	003

• Example db<>fiddle