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

My original data looks like:

Table1

and I want to end up with

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...

How do I split multiple columns of strings simultaneously?

CodePudding user response：

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:

• Example db<>fiddle

CodePudding user response：

you can use STRING_SPLIT for split strings,row_number for create ID in join and subquery for join as follows

select a.UserID,Type1,ProductID from 
(SELECT 
    UserID,value Type1 ,row_number() over (order by   value asc) rn
FROM Table1 
  cross apply  STRING_SPLIT(Type, ',')) a
  JOIN /*subquery*/
(SELECT 
    UserID,value ProductID,row_number() over (order by   value asc)   rn
FROM Table1
  cross apply  STRING_SPLIT(ProductID, ',')) b
  on a.rn=b.rn
  order by ProductID desc