question

alhowarthWF avatar image
0 Votes"
alhowarthWF asked alhowarthWF commented

Split multiple columns with delimited values into separate rows

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:
79752-image.png

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.
79714-image.png

Thanks


sql-server-transact-sql
image.png (13.7 KiB)
image.png (17.0 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Which output do you expect?

0 Votes 0 ·

Added/updated

0 Votes 0 ·

What version of SQL Server are you using?

0 Votes 0 ·

That would have been helpful. Added/updated.. SQL 2014

0 Votes 0 ·

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered alhowarthWF commented

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 |
 +---------+--------+-------+-------+
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you! That is exactly what I need.
I'll put some time into studying SQL-XML and cross apply.

0 Votes 0 ·

@alhowarthWF,

Good to hear that the proposed solution is working for you.
Please up-vote the following suggestion: https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali

0 Votes 0 ·
alhowarthWF avatar image alhowarthWF YitzhakKhabinsky-0887 ·

Upvoted...

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered alhowarthWF commented

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Tom. I failed to mention this is SQL Server 2014. It looks like STRING_SPLIT was added until 2016.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered alhowarthWF commented

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
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your suggestion! Unfortunately, it didn't produce all the options.

0 Votes 0 ·

Sorry, there were some typos in the previous code.. I updated it. Try it again.

79743-image.png


0 Votes 0 ·
image.png (16.6 KiB)

Yes, that works now. Thank you!

0 Votes 0 ·