SQL Server - splitting multiple rows using STRING_SPLIT function?

Question

Using SQL Server 2016.

I have the following table

 [ID]    [Name List1]   [Name List2]        [Name List3]
 1       a1,a2,a3       a21,a22             a31,a32,a33,a34,a45
 2       b1,b2          b21,b22,b23,b24     b31
 3       etc....

[ID] is a unique identifier column.

I need to split all these comma delimited fields into separate records.

What I did so far:

SELECT a.*,  b.[Name List1] FROM  [TABLE1] a LEFT JOIN
(SELECT DISTINCT [ID], value AS [Name List1]
FROM [TABLE1] CROSS APPLY STRING_SPLIT([Name List1], ',')
WHERE value IS NOT NULL AND rtrim(value) <> '') b ON a.[ID]=b.[ID]

This query will split records based on the first column, i.e. [Name List1] but I need to do it for all columns ([Name List2] and [Name List3] as well).

Is there an elegant way to achieve it with minimum coding?

Desired result should include all possible combinations of these comma delimited values:

 [ID]    [Name List1]   [Name List2]        [Name List3]
 1       a1             a21                 a31
 2       a2             a21                 a31
 3       a3             a21                 a31
 4     etc... meaning all possible combination of column splits

Answer 1

If you want to get all possible combinations, use STRING_SPLIT() and three CROSS APPLY operators:

Input:

CREATE TABLE #Data (
   ID int,
   [Name List1] varchar(100),
   [Name List2] varchar(100),
   [Name List3] varchar(100)
)
INSERT INTO #Data
   (ID, [Name List1], [Name List2], [Name List3])
VALUES   
   (1, 'a1,a2,a3', 'a21,a22',         'a31,a32,a33,a34,a45'),
   (2, 'b1,b2',    'b21,b22,b23,b24', 'b31')

T-SQL:

SELECT 
   d.ID, 
   s1.[value] AS [Name List1],
   s2.[value] AS [Name List2],
   s3.[value] AS [Name List3]
FROM #Data d
CROSS APPLY STRING_SPLIT(d.[Name List1], ',') s1
CROSS APPLY STRING_SPLIT(d.[Name List2], ',') s2
CROSS APPLY STRING_SPLIT(d.[Name List3], ',') s3

Output:

ID  Name List1  Name List2  Name List3
1   a1          a21         a31
1   a1          a21         a32
1   a1          a21         a33
1   a1          a21         a34
1   a1          a21         a45
1   a1          a22         a31
1   a1          a22         a32
1   a1          a22         a33
1   a1          a22         a34
1   a1          a22         a45
…

If you want to get all possible combinations with positions of each substring , then STRING_SPLIT() is not an option here, because this function returns a table with all substrings, but they are not ordered and the order of substrings is not guaranteed. One option in this case is to transform text into valid JSON array using REPLACE() and after that to use OPENJSON() with default schema to retrieve this JSON array as table, which has columns key, value and type (key column contains the index of the element in the specified array).

T-SQL:

SELECT 
   d.ID, 
   j1.[key] + 1 AS [Key List1], j1.[value] AS [Name List1],
   j2.[key] + 1 AS [Key List2], j2.[value] AS [Name List2],
   j3.[key] + 1 AS [Key List3], j3.[value] AS [Name List3]
FROM #Data d
CROSS APPLY OPENJSON('["' + REPLACE(d.[Name List1], ',', '","') + '"]') j1
CROSS APPLY OPENJSON('["' + REPLACE(d.[Name List2], ',', '","') + '"]') j2
CROSS APPLY OPENJSON('["' + REPLACE(d.[Name List3], ',', '","') + '"]') j3

Output:

ID  Key List1   Name List1  Key List2   Name List2  Key List3   Name List3
1   1           a1          1           a21         1           a31
1   1           a1          1           a21         2           a32
1   1           a1          1           a21         3           a33
1   1           a1          1           a21         4           a34
1   1           a1          1           a21         5           a45
1   1           a1          2           a22         1           a31
1   1           a1          2           a22         2           a32
1   1           a1          2           a22         3           a33
1   1           a1          2           a22         4           a34
1   1           a1          2           a22         5           a45
…