TSQL Select comma list for rows

Question

How to include a comma list box in a row and display it in a column?

For instance,

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

at

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

Answer 1

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

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

Fingerprints:

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

Answer 2

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

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)

Answer 3

, , , . Tally Table cteTally, , . , , .

, URL. http://www.sqlservercentral.com/articles/Tally+Table/72993/

While Loop, Recursive CTE XML VARCHAR (8000).

, "" ( ). - , Peter . tweek, Peter, , T-SQL VARCHAR (8000). VARCHAR (MAX), .