Splitting comma-separated string selecting as columns with joins in SQL Server?

Question

I have a table like this:

name     orderid
bambino  4,5,6,7,8

How can we separate values in orderid column into different columns like in example below?

name     order1 order2 order3 order4 order5
bambino  4      5      6      7      8

Answer 1

If you have a fixed amount of values in your orderid column, this question could maybe help you:

How to split a comma seperated value to columns

Answer 2

Some tricks with xml and then pivoting:

DECLARE @t TABLE
    (
      name VARCHAR(20) ,
      orderid VARCHAR(20)
    )

INSERT  INTO @t
VALUES  ( 'chao', '1,2,3' ),
        ( 'bambino', '4,5,6,7,8' );
WITH    cte
          AS ( SELECT   name ,
                        Split.a.value('.', 'VARCHAR(100)') AS orderid ,
                        ROW_NUMBER() OVER ( PARTITION BY name ORDER BY ( SELECT
                                                              NULL
                                                              ) ) rn
               FROM     ( SELECT    name ,
                                    CAST ('<M>' + REPLACE(orderid, ',',
                                                          '</M><M>') + '</M>' AS XML) AS orderid
                          FROM      @t
                        ) AS A
                        CROSS APPLY orderid.nodes('/M') AS Split ( a )
             )
    SELECT  name ,
            [1] AS order1 ,
            [2] AS order2 ,
            [3] AS order3 ,
            [4] AS order4 ,
            [5] AS order5 ,
            [6] AS order6 ,
            [7] AS order7 ,
            [8] AS order8 ,
            [9] AS order9 ,
            [10] AS order10
    FROM    cte PIVOT( MAX(orderid) FOR rn IN ( [1], [2], [3], [4], [5], [6],
                                                [7], [8], [9], [10] ) ) p

Output:

name    order1  order2  order3  order4  order5  order6  order7  order8  order9  order10
bambino 4       5       6       7       8       NULL    NULL    NULL    NULL    NULL
chao    1       2       3       NULL    NULL    NULL    NULL    NULL    NULL    NULL

Answer 3

Here is a really good webpage on how you can split an comma separated string into rows. There are many different approaches so pick a way that suits your needs. (Eg CLR will be faster than a XML 'hack', but this might not be a concern.)

Once you've got your data in rows you can then use PIVOT to turn it into columns. You might have to do this in dynamic SQL if you don't know how many columns you're going to have.

Answer 4

Most simplest and dynamic query for this, This query won't mind if you have million orders in orderid column

declare @name as nvarchar(50), @orderid as nvarchar(50), @dorders as nvarchar(max)='',@count as int = 1
select @name=name, @orderid=orderid from abc -- add here where clause to select particular row
select @dorders = @dorders + splitdata + ' as [Order'+convert(nvarchar(10),@count)+'],', @count = @count + 1 from fnsplitstring(@orderid,',')
set @dorders = SUBSTRING(@dorders,0,len(@dorders))
declare @Dsql as nvarchar(max) = 'select '''+@name+''' as name, '+@dorders
exec (@Dsql)

For your reference

Ohh.. And add this function, its helpful anyways ..

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END