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
