How do I expand comma separated values into separate rows using SQL Server 2005?

Question

I have a table that looks like this:

ProductId, Color
"1", "red, blue, green"
"2", null
"3", "purple, green"

And I want to expand it to this:

ProductId, Color
1, red
1, blue
1, green
2, null
3, purple
3, green

Whats the easiest way to accomplish this? Is it possible without a loop in a proc?

Answer 1

Take a look at this function. I've done similar tricks to split and transpose data in Oracle. Loop over the data inserting the decoded values into a temp table. The convent thing is that MS will let you do this on the fly, while Oracle requires an explicit temp table.

MS SQL Split Function
Better Split Function

Edit by author: This worked great. Final code looked like this (after creating the split function):

select pv.productid, colortable.items as color
from product p 
    cross apply split(p.color, ',') as colortable