I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a function (SQL Server 2016)?
Here's what I'm starting with:
GroupId Val1 Val2 ----------- --------- ------ G1 1,2,3 a,b,c G2 4,5,6 d,e,f G3 1,2,3,4 w,x,y,z
Here's what I would like to end up with:
GroupId Val1 Val2 ------- ------- ------- G1 1 a G1 2 b G1 3 c G2 4 d G2 5 e G2 6 f G3 1 w G3 2 x G3 3 y G3 4 z
Based on everything I've read, I think I may need to use the XML method because it allows me to specify an order, but I haven't been able to find an example of this method where multiple columns are split.
Here's the T-SQL to create the source table:
DROP TABLE IF EXISTS #TestData; CREATE TABLE #TestData(GroupId VARCHAR(10), Val1 VARCHAR(100), Val2 VARCHAR(100) ); INSERT INTO #TestData VALUES ('G1', '1,2,3', 'a,b,c'), ('G2', '4,5,6', 'd,e,f'), ('G3', '1,2,3,4', 'w,x,y,z'); SELECT * FROM #TestData;
Thank you in advance!