Split column values in a table separated with special character

Question

I have a table that looks like this:

Table 1

+----+--------+----------+
| ID | Name   |   SIZE   |      
+----+--------+----------+
| 1  | Style1 | S,M,L,XL |  
+----+--------+----------+

I need to iterate through the whole table and load it into another table that looks like the following without using SPLIT_STRING, we are using SQL Server 2012 without that capability.

I also know that the max values that would be inserted into the new table would be 100 columns wide. I'm still new to SQL and not very comfortable with functions and how to utilize them.

Table 2

+----+--------+-------+-------+-------+-------+-----+-------+
| ID |  NAME  | SIZE1 | SIZE2 | SIZE3 | SIZE4 | ... | SIZEN |
+----+--------+-------+-------+-------+-------+-----+-------+
|  1 | Style1 | S     | M     | L     | XL    |     |       |
+----+--------+-------+-------+-------+-------+-----+-------+

Thank you

Does this answer your question? [STRING\_SPLIT in SQL Server 2012](https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012) — Wouter, Aug 31 '20 at 14:42

Andrea · Accepted Answer · 2020-09-01T09:09:01.750

Since you are stuck with Sql Server 2012 you can use a bit of XML syntax.

This is a sample of code that should help you:

DECLARE @mockup TABLE (
    [id] INT
    ,[Name] VARCHAR(max)
    ,[Size] VARCHAR(max)
    );

INSERT INTO @mockup
VALUES (
    1,'Style'
    ,'S,M,L,XL'
    )

;WITH Splitted
AS (
    SELECT [id]
        ,[Name]
        ,[Size]
        ,CAST('<x>' + REPLACE([Size], ',', '</x><x>') + '</x>' AS XML) AS Parts
    FROM @mockup
    )
SELECT [id]
    ,[Name]
    ,Parts.value(N'/x[1]', 'varchar(5)') AS Column_1
    ,Parts.value(N'/x[2]', 'varchar(5)') AS Column_2
    ,Parts.value(N'/x[3]', 'varchar(5)') AS Column_3
    ,Parts.value(N'/x[4]', 'varchar(5)') AS Column_4
    ,Parts.value(N'/x[5]', 'varchar(5)') AS Column_5
    ,Parts.value(N'/x[6]', 'varchar(5)') AS Column_6
    --and so on until column 100
FROM Splitted;

Results:

Basically the code above transforms the content of your Size column into an xml fragment:

<x>S</x><x>M</x><x>L</x><x>XL</x>

Then you can extract each value in a different column using value() xml method (more info here).

Hi Andrea! how do i move the data into a XML format! thank you for all your help — ClaudeSAugilar, Aug 31 '20 at 18:55
@ClaudeSAugilar the code I posted already does the conversion (in the `CAST(... AS XML)` statement). I edited my answer to clarify that the phrase about xml conversion is just an explanation of how the code works. — Andrea, Sep 01 '20 at 09:05

critical_error · Answer 2 · 2020-08-31T22:44:01.153

The easiest way to do this--especially where the size column count varies--is with dynamic SQL. Here's an example I put together you can run in SSMS. It takes some of the same principles that @Andrea mentioned and expands on it.

SET NOCOUNT ON;

DECLARE @Sizes TABLE ( Id INT, Name VARCHAR(50), Sizes VARCHAR(50) );
INSERT INTO @Sizes ( Id, Name, Sizes ) VALUES
    ( 1, 'Style1', 'S,M,L,XL');

DECLARE @dynamic_sql VARCHAR(MAX);
SELECT @dynamic_sql = 'SELECT '
    +  CAST ( [Id] AS VARCHAR(10) ) + ' AS [ID],'
    + '''' + [Name] + ''' AS [NAME],'
    + List.Cols + ';'
FROM @Sizes
OUTER APPLY ( 
    SELECT CAST ( '<sizes><size>' + REPLACE ( Sizes, ',', '</size><size>' ) + '</size></sizes>' AS XML ) SizeXml
) AS x
OUTER APPLY (

    SELECT STUFF ( (

        SELECT
            ', ''' + Size + ''' AS [SIZE' + CAST ( SizeId AS VARCHAR(10) ) + ']' AS "text()"
        FROM (

            SELECT
                ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS SizeId,
                n.s.value ( '.', 'VARCHAR(50)' ) AS Size 
            FROM x.SizeXml.nodes('//sizes/size') n ( s )

        ) AS xml_list FOR XML PATH ('')

    ), 1, 2, '' ) AS Cols

) AS List
WHERE Id = 1;

-- Execute the dynamic SQL statement.
EXEC ( @dynamic_sql );

Returns

+----+--------+-------+-------+-------+-------+
| ID |  NAME  | SIZE1 | SIZE2 | SIZE3 | SIZE4 |
+----+--------+-------+-------+-------+-------+
|  1 | Style1 | S     | M     | L     | XL    |
+----+--------+-------+-------+-------+-------+

@ClaudeSAugilar I'm glad I could help. Please mark as the accepted answer if I did in fact answer your need. — critical_error, Sep 03 '20 at 15:59

Split column values in a table separated with special character

2 Answers2