I have a situation where for each ID in my table there are multiple fields that each contain a comma-delimited string. The strings don't necessarily have the same number of elements as one another. The format of the data comes from another team and I can't change it.
How can I split these strings into rows with SQL Server 2016? I'd rather not add a function to the database if it can be avoided.
Here's a sample of what I'm starting with and what I'm looking to achieve:
DECLARE @SourceTable TABLE (ID INT, String1 VARCHAR(100),
String2 VARCHAR(100), String3 VARCHAR(100))
INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
(2, 'd1,d2', 'e1', 'f1,f2')
--Source
SELECT *
FROM @SourceTable
Desired Output:
ID Position S1_Transformed S2_Transformed S3_Transformed
-- -------- -------------- -------------- --------------
1 1 a1 b1 c1
1 2 a2 b2 c2
1 3 a3 b3
1 4 b4
1 5 b5
1 6 b6
2 1 d1 e1 f1
2 2 d2 f2
EDIT: Corrected output based on Aaron's comment.