0

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
Improve this question
5
  • Maybe concatenate the lists together to be one big list and split that? Commented May 17, 2019 at 21:01
  • Please show the results that you want. It is not obvious. Commented May 17, 2019 at 21:04
  • I still need the column headers to be the same. I shall post also the desired output Commented May 17, 2019 at 21:05
  • 1
    You desired result isn't clear. It looks like you just want the first item of each list from the record with id = 1 and replicate that for all ids. That doesn't match the rest of the question. Please take the effort and post an actual result, that matches the posted sample data. Commented May 17, 2019 at 21:30
  • 1
    all possible combination of column splits - that is simply three cross apply in a row. [TABLE1] CROSS APPLY STRING_SPLIT([Name List1], ',') l1 CROSS APPLY STRING_SPLIT([Name List2], ',') l2 CROSS APPLY STRING_SPLIT([Name List3], ',') l3. Commented May 17, 2019 at 21:36

1 Answer 1

Reset to default
2

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
…
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you Zhorov for your detailed answer. Just realized though that I was not clear enough regarding the expected output. Let's put it this way: the query I posted, using STRING_SPLIT deals with the first column having CSV data. I can insert the result into a temporary table. Then I apply the same query to this temporary table but on the 2-nd column. Then again I repeat the process. The problem is that production table has 12 columns containing CSV strings and writing a stored procedure with repeating code is not an elegant solution. However... your code may achieve exactly the same thing?
@User123 Yes, If I understand you correctly. You'll achieve the same output if you execute one statement with APPLY operators for all 12 columns. And, of course, use DISTINCT if you want to get only unique combinations. Thanks.