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. Delimit/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)