TSQL Select comma list to rows

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

For example,

ID | Colou开发者_C百科r
------------
1  | 1,2,3,4,5

to:

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

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

Another possible workaround is to use XML (assuming you are working with SQL Server 2005 or greater):

DECLARE @s TABLE
    (
      ID INT
    , COLOUR VARCHAR(MAX)
    )

INSERT  INTO @s
VALUES  ( 1, '1,2,3,4,5' )

SELECT  s.ID, T.Colour.value('.', 'int') AS Colour
FROM    ( SELECT    ID
                  , CONVERT(XML, '<row>' + REPLACE(Colour, ',', '</row><row>') + '</row>') AS Colour
          FROM      @s a
        ) s
        CROSS APPLY s.Colour.nodes('row') AS T(Colour)

I know this is an older post but thought I'd add an update. Tally Table and cteTally table based splitters all have a major problem. They use concatenated delimiters and that kills their speed when the elements get wider and the strings get longer.

I've fixed that problem and wrote an article about it which may be found at he following URL. http://www.sqlservercentral.com/articles/Tally+Table/72993/

The new method blows the doors off of all While Loop, Recursive CTE, and XML methods for VARCHAR(8000).

I'll also tell you that a fellow by the name of "Peter" made an improvement even to that code (in the discussion for the article). The article is still interesting and I'll be updating the attachments with Peter's enhancements in the next day or two. Between my major enhancement and the tweek Peter made, I don't believe you'll find a faster T-SQL-Only solution for splitting VARCHAR(8000). I've also solved the problem for this breed of splitters for VARCHAR(MAX) and am in the process of writing an article for that, as well.