Expanding a comma delimited field

Good Morning
I am having difficulty working out a simple method to expand a table
looking like this
Index string field
1 hat,coat,umbrella
2 dog,cat,wolf
3 boat,car
Into a new table like this
Index single string
1 hat
1 coat
1 umbrella
2 dog
2 cat
2 wolf
3 boat
3 car
I have looked at Arrays and lists in SQL server
but its not clear.
Can You help
Many Thanks
Davdi Hills
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!David
there are a few sites around detailing the algorithms you can use eg have a look at Vyas's site: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Regards
Paul Ibison|||David
SELECT IDENTITY(INT) "num" INTO Numbers
FROM sysobjects s1
CREATE TABLE Test1
(
COL INT NOT NULL,
COL1 VARCHAR(100) NOT NULL
)
INSERT INTO Test1 VALUES (1,'A,B,C')
INSERT INTO Test1 VALUES (2,'D,E,F')
GO
SELECT * FROM Test1
SELECT col,SUBSTRING(COL1,num,CHARINDEX(',',COL1+',',num)-num)
FROM numbers n left join Test1 t on t.col< num+(SELECT MAX(col)FROM Test1)
WHERE SUBSTRING(','+col1,num,1)=','
order by col
DROP TABLE Test1
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:egXRAinMEHA.3400@.TK2MSFTNGP09.phx.gbl...
> Good Morning
> I am having difficulty working out a simple method to expand a table
> looking like this
> Index string field
> 1 hat,coat,umbrella
> 2 dog,cat,wolf
> 3 boat,car
>
> Into a new table like this
> Index single string
> 1 hat
> 1 coat
> 1 umbrella
> 2 dog
> 2 cat
> 2 wolf
> 3 boat
> 3 car
> I have looked at Arrays and lists in SQL server
> but its not clear.
> Can You help
> Many Thanks
> Davdi Hills
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!