tsql - How to split string and save into an array in T-SQL

Question

I am writing a cursor to populate data in new table from main table which contains data in below manner

Item    Colors
Shirt    Red,Blue,Green,Yellow

I want to populate new Table data by fetching the Item and then adding it in row, according to each color it contains

Item    Color
Shirt    Red
Shirt    Blue
Shirt    Green
Shirt    Yellow

I am stuck in how to

1) Delimate/Split "Colors" string 2) To save it in an array 3) To use it in cursor

as I am going to use Nested cursor for this purpose.

Answer 1

Using Sql Server 2005+ and the XML datatype, you can have a look at the following

DECLARE @Table TABLE(
        Item VARCHAR(250),
        Colors VARCHAR(250)
)

INSERT INTO @Table SELECT 'Shirt','Red,Blue,Green,Yellow'
INSERT INTO @Table SELECT 'Pants','Black,White'


;WITH Vals AS (
        SELECT  Item,
                CAST('<d>' + REPLACE(Colors, ',', '</d><d>') + '</d>' AS XML) XmlColumn
        FROM    @Table
)
SELECT  Vals.Item,
        C.value('.','varchar(max)') ColumnValue
FROM    Vals
CROSS APPLY Vals.XmlColumn.nodes('/d') AS T(C)