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 | Colour
------------
1 | 1,2,3,4,5

to:

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

Answers


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

Need Your Help

Recursively deleting folder in CodeIgniter FTP Class

php codeigniter ftp

I am using the CI FTP class, with the function delete_dir, its supposed to delete the folder and everything side of it, however, if the folder has files in it, it won't delete the folder and output...

Can't compile and run after renaming form and doing some additional work

c# .net-3.5 vb-power-pack

NOTE: This question has been renamed. Originally it targeted the fact that I was getting compile errors after deleting VB power pack shapes off my form, but it turned out that while that was when ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.