TSQL Select comma list to rows

Question

How do I turn a comma list field in a row and display it in a column?

For example,

ID | Colour
------------
1  | 1,2,3,4,5

to:

ID | Colour
------------
1  | 1 
1  | 2
1  | 3
1  | 4
1  | 5

Answer 1

The usual way to solve this is to create a split function. You can grab one from Google, for example this one from SQL Team. Once you have created the function, you can use it like:

create table colours (id int, colour varchar(255))
insert colours values (1,'1,2,3,4,5')

select  colours.id
,       split.data
from    colours
cross apply
        dbo.Split(colours.colour, ',') as split

This prints:

id    data
1     1
1     2
1     3
1     4
1     5