Split multiple columns with delimited values into separate rows

Question

Using SQL Server 2014.
I need to pull from a table, who's design and content are not in my control. This table has a server name and values for teams responsible for each area. The problem is, if multiple teams are entered for the same area of responsibility the values are concatenated with a semicolon. This is shown below in my sample data.

I need to look through certain columns for the combined values and split them into separate rows. I have code below that does this for one column. I cannot figure out how to do this for multiple columns.

DROP TABLE #SERVERSX;  
CREATE TABLE #SERVERSX(csn VARCHAR(100),ms VARCHAR(200),os VARCHAR(200),rb VARCHAR(200));  
INSERT INTO #SERVERSX VALUES ('server1','TeamA;TeamB','TeamX;TeamY','TeamM;TeamN');  
INSERT INTO #SERVERSX VALUES ('server2','TeamC;TeamA','TeamZ;TeamW','TeamO;TeamP');  
INSERT INTO #SERVERSX VALUES ('server3','TeamP;TeamX','TeamY','TeamN');  
SELECT * FROM #SERVERSX;  
---  
    ;with cte as (  
    select csn,  
    cast('<M>' + replace(ms, ';', '</M><M>') + '</M>' as XML) as Middle  
    ,os  
    ,rb  
    from #ServersX)  
  
 select csn,  
 split.a.value('.','varchar(200)') as Middle  
 ,os  
 ,rb  
 from cte  
 cross apply Middle.nodes('/M') split(a);  

Results:

As you can see, this works for the first field, MS/Middle. Yet, as I mentioned, I cannot figure out how to do the other columns.
I tried wrapping this query as a subquery, but the CTE causes an error. And if I did separate Selects for each column with a UNION ALL, I would end up with the values combined and split.

This would be my intended output, for the first two servers anyway.

Thanks

Answer 1

Hi @alhowarthWF ,

Please try the following solution.
It extends your method by covering all the columns by using XML and XQuery.
Also, it is optimized for performance.

SQL

-- DDL and sample data population, start  
DECLARE @SERVERSX TABLE (csn VARCHAR(100),ms VARCHAR(200),os VARCHAR(200),rb VARCHAR(200));  
INSERT INTO @SERVERSX VALUES   
('server1','TeamA;TeamB','TeamX;TeamY','TeamM;TeamN'),  
('server2','TeamC;TeamA','TeamZ;TeamW','TeamO;TeamP'),  
('server3','TeamP;TeamX','TeamY','TeamN');  
-- DDL and sample data population, end  
  
;WITH cte AS   
(  
	SELECT csn  
		, TRY_CAST('<M>' + REPLACE(ms, ';', '</M><M>') + '</M>' AS XML) AS Middle  
		, TRY_CAST('<M>' + REPLACE(os, ';', '</M><M>') + '</M>' AS XML) AS oss  
		, TRY_CAST('<M>' + REPLACE(rb, ';', '</M><M>') + '</M>' AS XML) AS rbb  
	FROM @ServersX  
)  
SELECT csn  
	, a.value('(./text())[1]','VARCHAR(200)') AS Middle  
	, b.value('(./text())[1]','VARCHAR(200)') AS os  
	, c.value('(./text())[1]','VARCHAR(200)') AS rb  
FROM cte  
	CROSS APPLY Middle.nodes('/M') AS t1(a)  
	CROSS APPLY oss.nodes('/M') AS t2(b)  
	CROSS APPLY rbb.nodes('/M') AS t3(c)  
ORDER BY 1,2,3,4;  

Output

+---------+--------+-------+-------+  
|   csn   | Middle |  os   |  rb   |  
+---------+--------+-------+-------+  
| server1 | TeamA  | TeamX | TeamM |  
| server1 | TeamA  | TeamX | TeamN |  
| server1 | TeamA  | TeamY | TeamM |  
| server1 | TeamA  | TeamY | TeamN |  
| server1 | TeamB  | TeamX | TeamM |  
| server1 | TeamB  | TeamX | TeamN |  
| server1 | TeamB  | TeamY | TeamM |  
| server1 | TeamB  | TeamY | TeamN |  
| server2 | TeamA  | TeamW | TeamO |  
| server2 | TeamA  | TeamW | TeamP |  
| server2 | TeamA  | TeamZ | TeamO |  
| server2 | TeamA  | TeamZ | TeamP |  
| server2 | TeamC  | TeamW | TeamO |  
| server2 | TeamC  | TeamW | TeamP |  
| server2 | TeamC  | TeamZ | TeamO |  
| server2 | TeamC  | TeamZ | TeamP |  
| server3 | TeamP  | TeamY | TeamN |  
| server3 | TeamX  | TeamY | TeamN |  
+---------+--------+-------+-------+  

Answer 2

In 2016+, you can use string_split.

 DROP TABLE IF EXISTS #SERVERSX;
 CREATE TABLE #SERVERSX(csn VARCHAR(100),ms VARCHAR(200),os VARCHAR(200),rb VARCHAR(200));
 INSERT INTO #SERVERSX VALUES ('server1','TeamA;TeamB','TeamX;TeamY','TeamM;TeamN');
 INSERT INTO #SERVERSX VALUES ('server2','TeamC;TeamA','TeamZ;TeamW','TeamO;TeamP');
 INSERT INTO #SERVERSX VALUES ('server3','TeamP;TeamX','TeamY','TeamN');

 SELECT 
    s.csn,
    msval.[value] as ms,
    osval.[value] as os,
    rbval.[value] as rb
 FROM #SERVERSX s
    CROSS APPLY string_split(ms,';') as msval
    CROSS APPLY string_split(os,';') as osval
    CROSS APPLY string_split(rb,';') as rbval

ORDER BY 1,2,3,4

Answer 3

If you only split the column to two fields, try this: (Based on your updated output, I updated the query)

;WITH CTE_Split_Columns AS (
    SELECT csn, 
           ms1 = CASE WHEN CHARINDEX(';', ms) > 0 THEN LEFT(ms, CHARINDEX(';', ms) - 1) ELSE ms END,
           ms2 = CASE WHEN CHARINDEX(';', ms) > 0 THEN RIGHT(ms, CHARINDEX(';', ms) - 1) ELSE NULL END,
           os1 = CASE WHEN CHARINDEX(';', os) > 0 THEN LEFT(os, CHARINDEX(';', os) - 1) ELSE os END,
           os2 = CASE WHEN CHARINDEX(';', os) > 0 THEN RIGHT(os, CHARINDEX(';', os) - 1) ELSE NULL END,
           rb1 = CASE WHEN CHARINDEX(';', rb) > 0 THEN LEFT(rb, CHARINDEX(';', rb) - 1) ELSE os END,
           rb2 = CASE WHEN CHARINDEX(';', rb) > 0 THEN RIGHT(rb, CHARINDEX(';', rb) - 1) ELSE NULL END
    FROM #SERVERSX
),
CTE_MS AS (
    SELECT csn, ms1 AS ms
    FROM CTE_Split_Columns
    UNION ALL
    SELECT csn, ms2 AS ms
    FROM CTE_Split_Columns
),
CTE_OS AS (
    SELECT csn, os1 AS os
    FROM CTE_Split_Columns
    UNION ALL
    SELECT csn, os2 AS os
    FROM CTE_Split_Columns
),
CTE_RB AS (
    SELECT csn, rb1 AS rb
    FROM CTE_Split_Columns
    UNION ALL
    SELECT csn, rb2 AS rb
    FROM CTE_Split_Columns
)

SELECT 
    s.csn,
    ms.ms,
    os.os,
    rb.rb
FROM #SERVERSX AS s
CROSS APPLY (SELECT csn, ms FROM CTE_MS WHERE csn = s.csn) AS ms
CROSS APPLY (SELECT csn, os FROM CTE_OS WHERE csn = s.csn) AS os
CROSS APPLY (SELECT csn, rb FROM CTE_RB WHERE csn = s.csn) AS rb
WHERE os.os IS NOT NULL AND rb.rb IS NOT NULL
ORDER BY s.csn, ms.ms