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

and a question that has been asked many times before... one such example: http://stackoverflow.com/questions/4250475/split-one-column-into-multiple-rows — Pero P., Dec 04 '10 at 08:46
@Mark Byers: This databases design is optimized for storing lists from something like a PHP website. It's horrible for SQL people but convenient for front end developers. — Andomar, Dec 04 '10 at 11:30

score 7 · Accepted Answer · answered Dec 04 '10 at 11:25

7

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:

answered Dec 04 '10 at 11:25

Andomar

232,371
49
380
404

select dbo.XW_Wheels.ID, dbo.Splitter(XW_Wheels.Colours, ',') from XW_Wheels cross apply dbo.Splitter(XW_Wheels.Colours, ',') as split AND I GET THIS MESSAGE "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitter", or the name is ambiguous." – Rya Dec 05 '10 at 11:37
select dbo.XW_Wheels.ID, split from XW_Wheels cross apply dbo.Splitter(XW_Wheels.Colours, ',') as split AND I GET "Invalid column name 'split'." – Rya Dec 05 '10 at 11:40
The function `dbo.Split` returns a table. Try to select `split.data` instead of just `split`. – Andomar Dec 05 '10 at 11:52
@Rya: Sounds like you're using a different Split function than the one from SQL Team. Use `select *` to see which column name it returns. – Andomar Dec 06 '10 at 12:11
ah, snap. value... split.value. Thank you Andomar, I could kiss you... (but I wont) :) – Rya Dec 06 '10 at 13:23

score 3 · Answer 2 · answered Dec 16 '10 at 18:05

3

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)

answered Dec 16 '10 at 18:05

Stuart Ainsworth

12,792
41
46

better yet:SELECT T.name.value('.', 'nvarchar(4000)') AS Colour FROM ( SELECT CONVERT(XML, '' + REPLACE(name, ',', '') + '') AS Name FROM (SELECT '1,2,3,4,5' name) a ) s CROSS APPLY s.Name.nodes('row') AS T(name) – kerem Jan 24 '15 at 13:48

score 1 · Answer 3 · answered May 08 '11 at 17:36

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.

TSQL Select comma list to rows

3 Answers3

Linked