Split a single delimited string into a table for a given number of columns

Question

I have a string that looks like this:

DECLARE @myString varchar(max) = '1,2,3,4,5,6'

I want to call a function (or in SOME way convert to a table) that takes as arguments the string, delimiter and an integer, and returns a table that splits the string into columns:

For instance,

SELECT * FROM somefunction(@mystring,',',2) should give me a table two columns:

1 2
3 4
5 6

AND

SELECT * FROM somefunction(@mystring,',',3) should give me three columns like so:

1 2 3
4 5 6

Answer 1

I want to reiterate firstly that this has a strong smell of a XY Problem and that very likely this isn't something you should be doing in the RDBMS. That isn't to say it can't be, but certainly SQL Server isn't the best place.

Secondly, as mentioned, this is impossible in a FUNCTION. A FUNCTION must be well defined, meaning the data in and the data out must be defined at the time the function is created. An object that needs to return a variable amount of columns isn't well defined, as you can't define the result until the object is called.

Also the only way to achieve what you are after is by using Dynamic SQL, which you cannot use inside a FUNCTION; as it requires using EXEC (which can only be used against a very few system objects and sp_executesql is not one of them).

This means we would need to use a Stored Procedure to achieve this, however, you won't be able to use syntax like SELECT * FROM dbo.MyProcedure(@mystring,',',2); You'll need to execute it (EXEC dbo.MyProcedure(@mystring,',',2);).

Before we get onto the end dynamic solution, we need to work out how we would do with a static value. That isn't too bad, you need to simply use a a string splitter that is ordinal position aware (STRING_SPLIT is not), so I am using DelimitedSplit8K_LEAD. Then you can use a bit of integer maths to assign the rows both a column and row group. Finally we can use those value to pivot the data, using a "Cross Tab".

For a non-dynamic approach, this gets us a result like this:

DECLARE @String varchar(8000),
        @Columns tinyint;

SET @String = '1,2,3,4,5,6';
SET @Columns = 3;

WITH Groupings AS(
    SELECT *,
           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) / @Columns AS RowNo,
           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) % @Columns +1 AS ColumnNo
    FROM dbo.DelimitedSplit8K_LEAD(@String,',') DS)
SELECT MAX(CASE G.ColumnNo WHEN 1 THEN G.Item END) AS Col1,
       MAX(CASE G.ColumnNo WHEN 2 THEN G.Item END) AS Col2,
       MAX(CASE G.ColumnNo WHEN 3 THEN G.Item END) AS Col3
FROM Groupings G
GROUP BY G.RowNo;

Of course, if you change the value of @Columns the number of columns does not change, it's hard coded.

It's also important to note I have used a varchar(8000) not a varchar(MAX). DelimitedSplit8K_LEAD (and DelimitedSplitN4K_LEAD) do not support MAX lengths, and the article above, and the original iteration of the function (DelimitedSplit8K) explain why.

Moving on, now we need to get onto the dynamic value. I'm going to assume that you won't have a "silly" value for @Columns, and that it'll be between 1 and 100. I'm also assuming you are using a recent version of SQL Server, and thus have access to STRING_AGG; if not you'll need to use FOR XML PATH (and STUFF) to do the aggregation of the dynamic statement.

First we can use a tally with up to 100 rows, to get the right number of column groups, and then (like mentioned) STRING_AGG to aggregate the dynamic part. The rest of the statement is still static. With the variable we have before, we end up with something like this to create the dynamic statement:

DECLARE @Delimiter nvarchar(20) = N',' + @CRLF + N'       ';

WITH Tally AS(
    SELECT TOP (@Columns)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N1(N)
         CROSS JOIN (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N2(N))
SELECT @SQL = N'WITH Groupings AS(' + @CRLF +
              N'    SELECT *,' + @CRLF +
              N'           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) / @Columns AS RowNo,' + @CRLF +
              N'           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) % @Columns +1 AS ColumnNo' + @CRLF +
              N'    FROM dbo.DelimitedSplit8K_LEAD(@String,'','') DS)' + @CRLF +
              N'SELECT ' + 
              (SELECT STRING_AGG(CONCAT(N'MAX(CASE G.ColumnNo WHEN ',T.I,N' THEN G.Item END) AS ',QUOTENAME(CONCAT(N'Col',T.I))),@Delimiter) WITHIN GROUP (ORDER BY T.I)
               FROM Tally T) + @CRLF +              
              N'FROM Groupings G' + @CRLF +
              N'GROUP BY G.RowNo;'
PRINT @SQL;

And the PRINT outputs the below (which is what we had before, excellent!):

WITH Groupings AS(
    SELECT *,
           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) / @Columns AS RowNo,
           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) % @Columns +1 AS ColumnNo
    FROM dbo.DelimitedSplit8K_LEAD(@String,',') DS)
SELECT MAX(CASE G.ColumnNo WHEN 1 THEN G.Item END) AS [Col1],
       MAX(CASE G.ColumnNo WHEN 2 THEN G.Item END) AS [Col2],
       MAX(CASE G.ColumnNo WHEN 3 THEN G.Item END) AS [Col3]
FROM Groupings G
GROUP BY G.RowNo;

Now we need to wrap this into a parametrised stored procedure, and also execute the dynamic statement. This gives us the following end result, using sys.sp_executesql to execute and parametrise the dynamic statement:

CREATE PROC dbo.DynamicPivot @String varchar(8000), @Delim char(1), @Columns tinyint, @SQL nvarchar(MAX) = NULL OUTPUT AS
BEGIN
    IF @Columns > 100
        THROW 72001, N'@Columns cannot have a value greater than 100.', 16;

    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    DECLARE @Delimiter nvarchar(20) = N',' + @CRLF + N'       ';

    WITH Tally AS(
        SELECT TOP (@Columns)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N1(N)
             CROSS JOIN (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N2(N))
    SELECT @SQL = N'WITH Groupings AS(' + @CRLF +
                  N'    SELECT *,' + @CRLF +
                  N'           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) / @Columns AS RowNo,' + @CRLF +
                  N'           (ROW_NUMBER() OVER (ORDER BY DS.ItemNumber) -1) % @Columns +1 AS ColumnNo' + @CRLF +
                  N'    FROM dbo.DelimitedSplit8K_LEAD(@String,@Delim) DS)' + @CRLF +
                  N'SELECT ' + 
                  (SELECT STRING_AGG(CONCAT(N'MAX(CASE G.ColumnNo WHEN ',T.I,N' THEN G.Item END) AS ',QUOTENAME(CONCAT(N'Col',T.I))),@Delimiter) WITHIN GROUP (ORDER BY T.I)
                   FROM Tally T) + @CRLF +              
                  N'FROM Groupings G' + @CRLF +
                  N'GROUP BY G.RowNo;'
    --PRINT @SQL;

    EXEC sys.sp_executesql @SQL, N'@String varchar(8000), @Delim char(1), @Columns tinyint', @String, @Delim, @Columns;
END;
GO

And then we can execute this like below:

EXEC dbo.DynamicPivot N'1,2,3,4,5,6',',',3; --Without @SQL
GO
DECLARE @SQL nvarchar(MAX)
EXEC dbo.DynamicPivot N'1 2 3 4 5 6 7 8 9 10',' ',5,@SQL OUTPUT; --With SQL to see statement run, and different delimiter
PRINT @SQL;

As I noted as well, and as you can see from the definition of the Procedure, if you try to pivot with more than 100 columns it will error:

--Will error, too many columns
EXEC dbo.DynamicPivot N'1,2,3,4,5,6',',',101; --Without @SQL

Which returns the error:

Msg 72001, Level 16, State 16, Procedure dbo.DynamicPivot, Line 5
@Columns cannot have a value greater than 100.

Edit: Noticed the value for the delimiter in the splitter needs to be parametrised too, so amended the SP's definition to add @Delim and added that the to dynamic SQL too and demonstrate in examples.