T-SQL -- convert comma-delimited column into multiple columns

Question

From the table below, how can I convert the Values column into multiple columns, populated with individual values that are currently separated by commas? Before the conversion:

Name  Values 
----  ------
John  val,val2,val3 
Peter val5,val7,val9,val14 
Lesli val8,val34,val36,val65,val71,val 
Amy   val3,val5,val99

The result of the conversion should look like:

Name  Col1  Col2  Col3  Col4  Col5  Col6 
----  ----  ----  ----  ----  ----  ----
John  val   val2  val3 
Peter val5  val7  val9  val14 
Lesli val8  val34 val36 val65 val71 val 
Amy   val3  val5  val99

Please format your tables a bit so we can make more sense of what you are asking. — Stu, Nov 17 '10 at 00:49
The image won't allow your question to be searchable. Indent your table samples by 4 spaces to preserve formatting... — Pero P., Nov 17 '10 at 17:23

score 3 · Accepted Answer · answered Nov 17 '10 at 02:19

First, what database product and version are you using? If you are using SQL Server 2005 and later, you can write a Split user-defined function like so:

CREATE FUNCTION [dbo].[Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter varchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, DataLength(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, DataLength(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , DataLength(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP (Coalesce(Len(@DelimitedList),1)) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.objects As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

You can then split out the values in you want using something akin to:

Select Name, Values
From Table1 As T1
Where Exists    (
                Select 1
                From Table2 As T2
                    Cross Apply dbo.Split (T1.Values, ',') As T1Values
                    Cross Apply dbo.Split (T2.Values, ',') As T2Values
                Where T2.Values.Value = T1Values.Value
                    And T1.Name = T2.Name
                )

score 3 · Answer 2 · edited Nov 13 '18 at 23:13

Here is a solution that uses a recursive cte to generate a "table of numbers" (courtesy of Itzik Ben-Gan), which is useful for all manner of problems including string splitting, and PIVOT. SQL Server 2005 onwards. Full table create, insert and select script included.

CREATE TABLE dbo.Table1 
(
    Name        VARCHAR(30),
    [Values]    VARCHAR(128)
)
GO

INSERT INTO dbo.Table1 VALUES ('John', 'val,val2,val3')
INSERT INTO dbo.Table1 VALUES ('Peter', 'val5,val7,val9,val14')
INSERT INTO dbo.Table1 VALUES ('Lesli', 'val8,val34,val36,val65,val71,val')
INSERT INTO dbo.Table1 VALUES ('Amy', 'val3,val5,val99')
GO

SELECT * FROM dbo.Table1;
GO

WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Name, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5, [6] AS Column6, [7] AS Column7
FROM
(SELECT Name,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY nums.n) AS PositionInList,
        LTRIM(RTRIM(SUBSTRING(valueTable.[Values], nums.n, charindex(N',', valueTable.[Values] + N',', nums.n) - nums.n))) AS [Value]
 FROM   Numbers AS nums INNER JOIN dbo.Table1 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.[Values])) AND SUBSTRING(N',' + valueTable.[Values], n, 1) = N',') AS SourceTable
PIVOT
(
MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5], [6], [7])
) AS Table2
GO

--DROP TABLE dbo.Table1

Which converts this output

Name   Values
John   val,val2,val3
Peter  val5,val7,val9,val14
Lesli  val8,val34,val36,val65,val71,val
Amy    val3,val5,val99

to

Name  Column1 Column2 Column3 Column4 Column5 Column6 Column7
Amy   val3    val5    val99   NULL    NULL    NULL    NULL
John  val     val2    val3    NULL    NULL    NULL    NULL
Lesli val8    val34   val36   val65   val71   val     NULL
Peter val5    val7    val9    val14   NULL    NULL    NULL

I should add that the shortcoming of this is that you can not dynamically resize the number of columns dependent on the max number of values in the comma delimited string. For that you would need to consider dynamic SQL and build up the number of columns to equal the max number of values in any one of the delimited strings. — Pero P., Nov 17 '10 at 18:13

T-SQL -- convert comma-delimited column into multiple columns

2 Answers2

Linked