Split string into table given row delimiter and column delimiter in SQL server

Question

How to split string containing matrix into table in SQL Server? String has columns and row delimiters.

Suppose I have a string:

declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';

Expected results (in three separate columns):

+---+---+---+
| A | B | C |
+---+---+---+
| D | E | F |
+---+---+---+
| X | Y | Z |
+---+---+---+

I am looking for general solution which has not defined number of columns and rows. So the string:

declare @str varchar(max)='A,B;D,E';

will be split into table with two columns:

+---+---+
| A | B |
+---+---+
| D | E |
+---+---+

My efforts. My first idea was to use dynamic SQL which turns the string into: insert into dbo.temp values (...) This approach although very fast has a minor drawback because it requires creating a table with the right number of columns first. I have presented this method in the answer to my own question below just to keep the question short.

Another idea would be to write down the string to a CSV file on the server and then bulk insert from it. Though I do not know how to do it and what would be performance of first and second idea.

The reason why I asked the question is because I want to import data from Excel to SQL Server. As I have experimented with different ADO approaches, this method of sending matrix-string is a landslide victory, especially when the length of the string increases. I asked a younger twin brother of the question here: Turn Excel range into VBA string where you will find suggestions how to prepare such a string from Excel range.

Bounty I decided to award Matt. I weighed highly Sean Lange's answer. Thank you Sean. I liked Matt's answer for its simplicity and shortness. Different approaches apart from Matt's and Sean's could be in parallel use so for the time being I am not accepting any answer (update: Finally, after a few months, I have accepted Matt's answer). I wish to thank Ahmed Saeed for his idea with VALUES, for it is a nice evolution of the answer I began with. Of course, it is no match for the Matt's or Sean's. I upvoted every answer. I will appreciate any feedback from you on using these methods. Thank you for the quest.

Answer 1

OK this puzzle intrigued me so I decided to see if I could do this without any looping. There are a couple of prerequisites for this to work. The first is we will assume you have some sort of tally table. In case you don't have that here is the code for mine. I keep this on every system I use.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

The second piece of this puzzle is needing a set based string splitter. My preference for this is the uber fast Jeff Moden splitter. One caveat is that it will only work with varchar values up to 8,000. This is plenty for most delimited strings I work with. You can find Jeff Moden's splitter (DelimitedSplit8K) here.

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

Last but not least is that the technique I am using here is a dynamic cross tab. This is something else I learned from Jeff Moden. He has a great article on the subject here.

http://www.sqlservercentral.com/articles/Crosstab/65048/

Putting all of this together you can come up with something like this which will be really fast and will scale well.

declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';

declare @StaticPortion nvarchar(2000) = 
'declare @str varchar(max)=''' + @str + ''';with OrderedResults as
    (
        select s.ItemNumber
            , s.Item as DelimitedValues
            , x.ItemNumber as RowNum
            , x.Item
        from dbo.DelimitedSplit8K(@str, '';'') s
        cross apply dbo.DelimitedSplit8K(s.Item, '','') x
    )
    select '

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults group by ItemNumber';

select @DynamicPortion = @DynamicPortion + 
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then Item end) as Column' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= (select MAX(len(Item) - LEN(replace(Item, ',', ''))) + 1
                from dbo.DelimitedSplit8K(@str, ';')
            )

declare @SqlToExecute nvarchar(max) = @StaticPortion + stuff(@DynamicPortion, 1, 1, '') + @FinalStaticPortion
exec sp_executesql @SqlToExecute

--EDIT--

Here is the DelimitedSplit8K function in case the link becomes invalid.

ALTER FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
   FROM cteStart s
;

Answer 2

One of the easier ways is to convert the string to XML based on replacing your delimiters.

declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';
DECLARE @xmlstr XML
SET @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)

SELECT
    t.n.value('col[1]','CHAR(1)') as Col1
    ,t.n.value('col[2]','CHAR(1)') as Col2
    ,t.n.value('col[3]','CHAR(1)') as Col3
FROM
    @xmlstr.nodes ('/rows/row') AS t(n)

• Format string as XML <rows><row><col></col><col></col></row><row><col></col><col></col></row></rows> Basically you need to add the beginning and ending tags then replace the column delimiter with the column tags and the row delimiter with both column and row tags
• .nodes is a method on the xml data type that "is useful when you want to shred an xml data type instance into relational data" https://msdn.microsoft.com/en-us/library/ms188282.aspx
• as t(n) tells you how you will end up accessing the XML row and column. t being the table alias and n being the node alias (kind of like a row). so t.n.value() gets a particular row
• COL[1] means get the first COL tag in the row it is 1 based so 2 is the next then 3 etc.
• CHAR(1) is a datatype definition meaning 1 character and was based on your example data having only 1 character per column. you may noticed I made it VARCHAR(MAX) in the dynamic query because if data type is unknown then you will want more flexibility.

Or dynamically

DECLARE @str varchar(max)='A,B,C,D,E;F,G,H,I,J;K,L,M,N,O';
DECLARE @NumOfColumns INT
SET @NumOfColumns = (LEN(@str) - LEN(REPLACE(@str,',',''))) / (LEN(@str) - LEN(REPLACE(@str,';','')) + 1) + 1

DECLARE @xmlstr XML
SET @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)

DECLARE @ParameterDef NVARCHAR(MAX) = N'@XMLInputString xml'
DECLARE @SQL NVARCHAR(MAX) = 'SELECT '

DECLARE @i INT = 1

WHILE @i <= @NumOfColumns
BEGIN
    SET @SQL = @SQL + IIF(@i > 1,',','') + 't.n.value(''col[' + CAST(@i AS VARCHAR(10)) + ']'',''NVARCHAR(MAX)'') as Col' + CAST(@i AS VARCHAR(10))

    SET @i = @i + 1
END

SET @SQL = @SQL + ' FROM
    @XMLInputString.nodes (''/rows/row'') AS t(n)'

EXECUTE sp_executesql @SQL,@ParameterDef,@XMLInputString = @xmlstr

Answer 3

Below code should work in SQL Server. It uses Common Table Expression and Dynamic SQL with little manipulations. Just assign the string value to @str variable and execute the complete code in one go. Since it uses CTE, it is easy to analyze data at each step.

Declare @Str varchar(max)= 'A,B,C;D,E,F;X,Y,Z';

IF OBJECT_ID('tempdb..#RawData') IS NOT NULL
    DROP TABLE #RawData;
;WITH T_String AS
(
    SELECT  RIGHT(@Str,LEN(@Str)-CHARINDEX(';',@Str,1)) AS RawString, LEFT(@Str,CHARINDEX(';',@Str,1)-1) AS RowString, 1 AS CounterValue,  len(@Str) - len(replace(@Str, ';', '')) AS RowSize
    --
    UNION ALL
    --
    SELECT  IIF(CHARINDEX(';',RawString,1)=0,NULL,RIGHT(RawString,LEN(RawString)-CHARINDEX(';',RawString,1))) AS RawString, IIF(CHARINDEX(';',RawString,1)=0,RawString,LEFT(RawString,CHARINDEX(';',RawString,1)-1)) AS RowString, CounterValue+1 AS CounterValue, RowSize AS RowSize
    FROM    T_String AS r
    WHERE   CounterValue <= RowSize
)
,T_Columns AS
(
    SELECT  RowString AS RowValue, RIGHT(a.RowString,LEN(a.RowString)-CHARINDEX(',',a.RowString,1)) AS RawString, 
            LEFT(a.RowString,CHARINDEX(',',a.RowString,1)-1) AS RowString, 1 AS CounterValue,  len(a.RowString) - len(replace(a.RowString, ',', '')) AS RowSize
    FROM    T_String AS a
    --WHERE a.CounterValue = 1
    --
    UNION ALL
    --
    SELECT  RowValue, IIF(CHARINDEX(',',RawString,1)=0,NULL,RIGHT(RawString,LEN(RawString)-CHARINDEX(',',RawString,1))) AS RawString, IIF(CHARINDEX(',',RawString,1)=0,RawString,LEFT(RawString,CHARINDEX(',',RawString,1)-1)) AS RowString, CounterValue+1 AS CounterValue, RowSize AS RowSize
    FROM    T_Columns AS r
    WHERE   CounterValue <= RowSize
)
,T_Data_Prior2Pivot AS 
(
    SELECT  c.RowValue, c.RowString, c.CounterValue
    FROM    T_Columns AS c
    INNER JOIN
            T_String AS r
        ON  r.RowString = c.RowValue
)
SELECT  *
INTO    #RawData
FROM    T_Data_Prior2Pivot;

DECLARE @columnNames VARCHAR(MAX)
        ,@sqlQuery VARCHAR(MAX)
SELECT @columnNames = COALESCE(@columnNames+', ['+CAST(CounterValue AS VARCHAR)+']','['+CAST(CounterValue AS VARCHAR)+']') FROM (SELECT DISTINCT CounterValue FROM #RawData) T
PRINT @columnNames

SET @sqlQuery = '
SELECT  '+@columnNames+'
FROM    ( SELECT * FROM #RawData 
        ) AS b
PIVOT   (MAX(RowString) FOR CounterValue IN ('+@columnNames+')) AS p
'

EXEC (@sqlQuery);

Below is Stats screenshot for above query from http://statisticsparser.com/.

Answer 4

**--Using dynamic queries..**


declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';

declare @cc int

select @cc = len (substring (@str, 0, charindex(';', @str))) - len(replace(substring (@str, 0, charindex(';', @str)), ',', ''))

declare @ctq varchar(max) = 'create table t('


declare @i int = 0 

while @i <= @cc
begin

        select @ctq = @ctq + 'column' + char(65 + @i) + ' varchar(max), '
        select  @i = @i + 1
end

select  @ctq = @ctq + ')'

select  @str = '''' + replace(@str, ',', ''',''') + ''''

select @str = 'insert t select ' + @str

select  @str = replace (@str, ';', ''' union all select ''')

exec(@ctq)

exec(@str)

Answer 5

I post the answer to my question just to extend the question, to show what I use at the time when I ask the question.

The idea is to change the original string into:

insert into dbo.temp values (...)(...)

Here is a stored procedure for that:

create PROC [dbo].[StringToMatrix] 
(
 @String nvarchar(max)
,@DelimiterCol nvarchar(50)=','
,@DelimiterRow nvarchar(50)=';'
,@InsertTable nvarchar(200) ='dbo.temp'
,@Delete int=1 --delete is ON
) 
AS
BEGIN
set nocount on;

set @String = case when right(@String,len(@DelimiterRow))=@DelimiterRow then left(@string,len(@String)-len(@DelimiterRow)) else @String end --if present, removes the last row delimiter at the very end of string
set @String = replace(@String,@DelimiterCol,''',''')
set @String = replace(@String,@DelimiterRow,'''),'+char(13)+char(10)+'(''')   
set @String = 'insert into '+@InsertTable+' values '+char(13)+char(10)+'(''' +@String +''');'
set @String = replace(@String,'''''','null') --optional, changes empty strings to nulls

set @String = CASE 
    WHEN @Delete = 1 THEN 'delete from '+@InsertTable+';'+char(13)+char(10)+@String 
    ELSE @String 
    END

--print @String
exec (@String)
END

Executing the proc with the code:

exec [dbo].[StringToMatrix] 'A,B,C;D,E,F;X,Y,Z'

generates the following @String:

delete from [dbo].[temp];
insert into [dbo].[temp] values 
('A','B','C'),
('D','E','F'),
('X','Y','Z');

which in final line of proc is dynamically executed.

The solution requires creating appropriate dbo.table first, to which the values will be inserted. It is a minor drawback. Thus the solution is not so dynamic as it could be if it had a structure: select * into dbo.temp. Nevertheless I want to share this solution because it works, it is fast, simple, and maybe it will be an inspiration for some other answers.

Answer 6

Here is yet another approach.

Declare @Str varchar(max)='A,B,C;D,E,F;X,Y,Z';

Select A.*,B.*
 Into  #TempSplit
 From (Select RowNr=RetSeq,String=RetVal From [dbo].[udf-Str-Parse](@Str,';')) A
 Cross Apply [dbo].[udf-Str-Parse](A.String,',') B

Declare @SQL varchar(max) = ''
Select @SQL = @SQL+Concat(',Col',RetSeq,'=max(IIF(RetSeq=',RetSeq,',RetVal,null))') 
 From  (Select Distinct RetSeq from #TempSplit) A 
 Order By A.RetSeq

Set @SQL ='
If Object_ID(''[dbo].[Temp]'', ''U'') IS NOT NULL 
  Drop Table [dbo].[Temp]; 

Select ' + Stuff(@SQL,1,1,'') + ' Into [dbo].[Temp] From #TempSplit  Group By RowNr Order By RowNr 
'
Exec(@SQL)

Select * from Temp

Returns

Col1    Col2    Col3
A       B       C
D       E       F
X       Y       Z

Now, this does require a parser which is listed below:

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Just to illustrate, the First Parse will return

RowNr   String
1       A,B,C
2       D,E,F
3       X,Y,Z

This is then parsed again via a CROSS APPLY, which returns the following and is stored in a temp table

RowNr   String  RetSeq  RetVal
1       A,B,C   1       A
1       A,B,C   2       B
1       A,B,C   3       C
2       D,E,F   1       D
2       D,E,F   2       E
2       D,E,F   3       F
3       X,Y,Z   1       X
3       X,Y,Z   2       Y
3       X,Y,Z   3       Z

EDIT: Or Just for fun

Declare @String varchar(max)='A,B,C;D,E,F;X,Y,Z';

Declare @SQL varchar(max) = '',@Col int = Len(Left(@String,CharIndex(';',@String)-1))-Len(replace(Left(@String,CharIndex(';',@String)-1),',',''))+1
Select  @SQL = @SQL+SQL From (Select Top (@Col) SQL=Concat(',xRow.xNode.value(''col[',N,']'',''varchar(max)'') as Col',N) From (Select N From (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) N(N) ) N ) A
Select  @SQL = Replace('Declare @XML XML = Cast((''<row><col>'' + Replace(Replace(''[getString]'','';'',''</col></row><row><col>''),'','',''</col><col>'') + ''</col></row>'') as XML);Select '+Stuff(@SQL,1,1,'')+' From @XML.nodes(''/row'') AS xRow(xNode) ','[getString]',@String)
Exec (@SQL)

Returns

Col1    Col2    Col3
A       B       C
D       E       F
X       Y       Z

Answer 7

This problem can be solved without the need for temporary tables, views, loops or xml. First you can create a string splitter function based on a tally table as per example below:

ALTER FUNCTION [dbo].[SplitString]
(
   @delimitedString VARCHAR(MAX),
   @delimiter VARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@delimitedString,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@delimitedString,t.N,1) = @delimiter OR t.N = 0))
  SELECT  ROW_NUMBER() OVER (ORDER BY s.N1) AS Nr
         ,Item = SUBSTRING(@delimitedString, s.N1, ISNULL(NULLIF(CHARINDEX(@delimiter,@delimitedString,s.N1),0)-s.N1,8000))
    FROM cteStart s;

Then use the splitter function to first split the strings based on the row delimiters. Then apply the splitter function again on each row using the OUTER APPLY statement. Finally pivot the result. Since the number of columns is unknown the query will have to be executed as dynamic SQL as per example below:

DECLARE @source VARCHAR(max) = 'A1,B1,C1,D1,E1,F1,G1;A2,B2,C2,D2,E2,F2,G2;A3,B3,C3,D3,E3,F3,G3;A4,B4,C4,D4,E4,F4,G4;A5,B5,C5,D5,E5,F5,G5;A6,B6,C6,D6,E6,F6,G6;A7,B7,C7,D7,E7,F7,G7;A8,B8,C8,D8,E8,F8,G8;A9,B9,C9,D9,E9,F9,G9;A10,B10,C10,D10,E10,F10,G10;A11,B11,C11,D11,E11,F11,G11;A12,B12,C12,D12,E12,F12,G12;A13,B13,C13,D13,E13,F13,G13;A14,B14,C14,D14,E14,F14,G14;A15,B15,C15,D15,E15,F15,G15;A16,B16,C16,D16,E16,F16,G16;A17,B17,C17,D17,E17,F17,G17;A18,B18,C18,D18,E18,F18,G18;A19,B19,C19,D19,E19,F19,G19;A20,B20,C20,D20,E20,F20,G20'

-- First determine the columns names. Since the string can be potential very long we don’t want to parse the entire string to determine how many columns 
-- we have, instead get sub string of main string up to first row delimiter.
DECLARE @firstRow VARCHAR(max) = LEFT(@source, CHARINDEX(';', @source) - 1);
DECLARE @columnNames NVARCHAR(MAX) = '';

-- Use string splitter function on sub string to determine column names.
SELECT @columnNames = STUFF(( 
                                SELECT ',' + QUOTENAME(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS VARCHAR(10)))
                                FROM        [dbo].[SplitString](@firstRow, ',') Items
                                FOR XML PATH('')), 1, 1, '');

-- Next build dynamic query that will generate our matrix table.
-- CTE first split string by row delimiters then it applies the string split function again on each row.  
DECLARE @pivotQuery NVARCHAR(MAX) ='
;WITH CTE_SplitData AS
(
SELECT       R.Nr AS [Row]
            ,C.[Columns]
            ,ROW_NUMBER() OVER (PARTITION BY R.Nr ORDER BY R.Item) AS ColumnNr
FROM        [dbo].[SplitString](@source, '';'') R
OUTER APPLY (
                SELECT  Item AS [Columns]
                FROM    [dbo].[SplitString](R.Item, '','') 
            ) C
)
-- Pivoted reuslt
SELECT * FROM
(  
     SELECT * 
     FROM   CTE_SplitData
)as T
PIVOT 
(
     max(T.[Columns])
     for T.[ColumnNr] in (' +  @columnNames + ')
) as P'


EXEC sp_executesql  @pivotQuery,
          N'@source VARCHAR(MAX)',  
          @source = @source;        -- Pass the source string to be split as a parameter to the dynamic query.

Answer 8

Some XML with pivoting and dynamic SQL.

1. Replace , and ; with tags p and row, to cast it as XML,

2. Then count the number of columns and put it in @i,

3. With colsPiv CTE we generate a string and put it into @col, string is like ,[1],[2],..[n] it will be used in pivoting,

4. Than we generate a dynamic pivot query, and execute it. We also pass 2 parameters XML and column count.

Here is the query:

--declare @str varchar(max)='A,B;D,E;X,Y',
declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z',
        @x xml,
        @col nvarchar(max),
        @sql nvarchar(max),
        @params nvarchar(max) = '@x xml, @i int',
        @i int

SELECT  @x = CAST('<row>'+REPLACE(('<p>'+REPLACE(@str,',','</p><p>')+'</p>'),';','</p></row><row><p>')+'</row>' as xml),
        @str = REPLACE(@str,';',',;')+',;', 
        @i =  (LEN(@str)-LEN(REPLACE(@str,',','')))/(LEN(@str)-LEN(REPLACE(@str,';','')))

;WITH colsPiv AS (
    SELECT 1 as col
    UNION ALL
    SELECT col+1
    FROM colsPiv
    WHERE col < @i
)

SELECT @col = (
    SELECT ','+QUOTENAME(col)
    FROM colsPiv
    FOR XML PATH('')
)

SELECT @sql = N'
;WITH cte AS (
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum,
            t.c.value(''.'',''nvarchar(max)'') as [Values]
    FROM @x.nodes(''/row/p'') as t(c)
)

SELECT '+STUFF(@col,1,1,'')+'
FROM (
    SELECT  RowNum - CASE WHEN RowNum%@i = 0 THEN @i ELSE RowNum%@i END Seq ,
            CASE WHEN RowNum%@i = 0 THEN @i ELSE RowNum%@i END as [ColumnNum],
            [Values]
    FROM cte
) as t
PIVOT (
    MAX([Values]) FOR [ColumnNum] IN ('+STUFF(@col,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql, @params, @x = @x, @i = @i

Output for A,B,C;D,E,F;X,Y,Z:

1   2   3
A   B   C
D   E   F
X   Y   Z

For A,B;D,E;X,Y:

1   2
A   B
D   E
X   Y

Answer 9

in this solution I'll use string manipulation as much as possible. The procedure will construct a dynamic SQL statement by converting the input string to a form suitable for VALUES keyword, build the column titles by counting the number of columns and generate the required headers. Then, simply execute the constructed SQL statement.

Create Proc dbo.Spliter
(
    @str varchar(max), @RowSep char(1), @ColSep char(1)
) 
as
    declare  @FirstRow varchar(max), @hdr varchar(max), @n int, @i int=0

-- Generate the Column names
    set @FirstRow=iif(CHARINDEX(@RowSep, @str)=0, @str, Left(@str, CHARINDEX(@RowSep, @str)-1))

    set @n=LEN(@FirstRow) - len(REPLACE(@FirstRow, @ColSep,''))
    while @i<=@n begin
        Set @hdr= coalesce(@hdr+', ', '') + 'Col' +convert(varchar, @i)
        set @i+=1
    end

--Convert the input string to a form suitable for Values keyword
--i.e. similar to Values(('A'),('B'),('C')),(('D'),('E'),('F')), ...etc
    set @str =REPLACE(@str, @ColSep,'''),(''')
    set @str = 'Values((''' + REPLACE(@str, @RowSep, ''')),((''') + '''))'

    exec('SELECT * FROM (' + @str + ') as t('+@hdr+')')    

-- exec dbo.Spliter 'A,B,C;D,E,F;X,Y,Z', ';', ','

Method-2:

To overcome the problem of the 1000 rows Values limit as indicated by PrzemyslawRemin, here is a small modification to convert input string to one row xml field and then CROSS APPLY it with its individual elements.

Create Proc dbo.Spliter2
(
    @str varchar(max), @RowSep char(1), @ColSep char(1)
) 
as

     declare  @FirstRow varchar(max), @hdr varchar(max), @ColCount int, @i int=0
 , @ColTemplate varchar(max)= 'Col.value(''(./c)[$]'', ''VARCHAR(max)'') AS Col$'

-- Determin the number of columns
    set @FirstRow=iif(CHARINDEX(@RowSep, @str)=0, @str, Left(@str, CHARINDEX(@RowSep, @str)-1))
    set @ColCount = LEN(@FirstRow) - len(REPLACE(@FirstRow, @ColSep,''))

-- Construct Column Headers by replacing the $ with the column number
-- similar to: Col.value('(./c)[1]', 'VARCHAR(max)') AS Col1,     Col.value('(./c)[2]', 'VARCHAR(max)') AS Col2
    while @i<=@ColCount begin
        Set @hdr= coalesce(@hdr+', ', '') + Replace(@ColTemplate, '$', convert(varchar, @i+1))
        set @i+=1
    end

-- Convert the input string to XML format
-- similar to '<r><c>A</c><c>B</c><c>c</c></r> <r><c>D</c><c>E</c><c>f</c>    </r> 
    set @str='<c>'+replace(@str, ',', '</c>'+'<c>')+'</c>'
    set @str='<r>'+replace(@str  , ';', '</c></r><r><c>')+'</r>'

    set @str='SELECT ' +@HDR 
    + ' From(Values(Cast('''+@str+''' as xml))) as t1(x) 
        CROSS APPLY x.nodes(''/r'') as t2(Col)'

    exec( @str)

-- exec dbo.Spliter2 'A,B,C;D,E,F;X,Y,Z', ';', ','

Answer 10

Here is a method to do this via a dynamic PIVOT using a Split custom function:

Split Function

CREATE FUNCTION [dbo].[fn_Split](@text varchar(MAX), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY,
  value varchar(MAX)   
)
AS
BEGIN

DECLARE @index int 
SET @index = -1 

WHILE (LEN(@text) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @Strings VALUES (@text)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN
END

GO

Query

Declare @Str Varchar (Max) = 'A,B,C;D,E,F;X,Y,Z';
Declare @Sql NVarchar (Max) = '',
        @Cols NVarchar (Max) = '';

;With Rows As
(
    Select      Position, Value As Row
    From        dbo.fn_Split(@str, ';')
), Columns As
(
    Select      Rows.Position   As RowNum,
                Cols.Position   As ColNum,
                Cols.Value      As ColValue 
    From        Rows
    Cross Apply dbo.fn_Split(Row, ',') Cols
)
Select  *
Into    #Columns
From    Columns

Select  @Cols = Stuff(( Select  Distinct ',' + QuoteName(ColNum)
                        From    #Columns
                        For Xml Path(''), Type).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '')

Select  @SQL = 'SELECT ' + @Cols + ' FROM #Columns 
Pivot 
(
    Max(ColValue)
    For ColNum In (' + @Cols + ')
) P
Order By RowNum'

Execute (@SQL)

Results

1   2   3
A   B   C
D   E   F
X   Y   Z

Answer 11

My solution is using string_split and stuff.. First is example on how it works

DECLARE @str varchar(max) = 'A,B,C;D,E,F;X,Y,Z';
;WITH cte
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, *
FROM string_split(@str, ';')),
cte2
AS (SELECT rn, ROW_NUMBER() OVER (PARTITION BY rn ORDER BY (SELECT NULL)) rownum, val.value
FROM cte c
CROSS APPLY string_split(value, ',') val)
SELECT
    [1], [2], [3]
FROM cte2
PIVOT (MAX(value) FOR rownum IN ([1], [2], [3])) p

Using dynamic sql we can identify the list of columns and it will work for any input

declare @str varchar(max)='A,B;D,E;X,Y';
declare @sql nvarchar(max)
declare @cols varchar(max) = ''

;with cte as (
select row_number() over(order by (select null)) rn from string_split( substring(@str,1,charindex(';', @str)-1),',')
) select @cols=concat(@cols,',',quotename(rn)) from cte

select @cols = stuff(@cols,1,1,'')
set @sql = N'
declare @str varchar(max)=''A,B;D,E;X,Y'';
with cte as
(
select row_number() over( order by (select null)) as rn, * from string_split(@str,'';'')
), cte2 as (
select rn, row_number() over(partition by rn order by (select null)) rownum,  val.value from cte c cross apply string_split(value,'','') val
)
select ' +@cols + '
from cte2 
pivot (max(value) for rownum in (' + @cols + ')) p '

exec sp_executesql @sql

If you are using SQL Server <2016 then we can write our own split function

Answer 12

The following isn't quite what the OP asked, but it's been handy for me, to export a spreadsheet as CSV (actually Tab-SV) with column headings, and convert it to a SQL Table with correct column names.

IF OBJECT_ID('dbo.uspDumpMultilinesWithHeaderIntoTable', 'P') IS NOT NULL 
    DROP PROCEDURE dbo.uspDumpMultilinesWithHeaderIntoTable; 
GO
CREATE PROCEDURE dbo.uspDumpMultilinesWithHeaderIntoTable @TableName VARCHAR(32), @Multilines VARCHAR(MAX)
AS
    SET NOCOUNT ON
    IF OBJECT_ID('tempdb..#RawData') IS NOT NULL DROP TABLE #RawData
    IF OBJECT_ID('tempdb..#RawDataColumnnames') IS NOT NULL DROP TABLE #RawDataColumnnames
    DECLARE @RowDelim VARCHAR(9) = '
'
    DECLARE @ColDelim VARCHAR(9) = CHAR(9)
    DECLARE @MultilinesSafe VARCHAR(MAX)
    DECLARE @MultilinesXml XML--VARCHAR(MAX)
    DECLARE @ColumnNamesAsString VARCHAR(4000)
    DECLARE @SQL NVARCHAR(4000), @ParamDef NVARCHAR(4000)

    SET @MultilinesSafe = REPLACE(@Multilines, CHAR(10), '')    -- replace LF
    SET @MultilinesSafe = (SELECT REPLACE(@MultilinesSafe, CHAR(10), '') FOR XML PATH(''))   -- escape any XML confusion
    SET @MultilinesSafe = '<rows><row first="1"><cols><col first="1">' + REPLACE(REPLACE(@MultilinesSafe, @RowDelim, '</col></cols></row><row first="0"><cols><col first="0">'), @ColDelim, '</col><col>') + '</col></cols></row></rows>'
    SET @MultilinesXml = @MultilinesSafe
    --PRINT CAST(@MultilinesXml AS VARCHAR(MAX))

    -- extract Column names
    SELECT
        IDENTITY(INT, 1, 1) AS ID,
        t.n.query('.').value('.', 'VARCHAR(4000)') AS ColName
    INTO #RawDataColumnnames
    FROM @MultilinesXml.nodes('/rows/row[@first="1"]/cols/col') AS t(n) -- just first row
    ALTER TABLE #RawDataColumnnames ADD CONSTRAINT [PK_#RawDataColumnnames] PRIMARY KEY CLUSTERED(ID)
    -- now tidy any strange characters in column name
    UPDATE T SET ColName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ColName, '.', '_'), ' ', '_'), '[', ''), ']', ''), '.', ''), '$', '') FROM #RawDataColumnnames T

    -- create output table
    SET @SQL = 'IF OBJECT_ID(''' + @TableName + ''') IS NOT NULL DROP TABLE ' + @TableName
    --PRINT 'TableDelete SQL=' + @SQL
    EXEC sp_executesql  @SQL

    SET @SQL = 'CREATE TABLE ' + @TableName + '('
    SELECT @SQL = @SQL + CASE T.ID WHEN 1 THEN '' ELSE ', ' END
        + CHAR(13) + '['+ T.ColName + '] VARCHAR(4000) NULL'
    FROM #RawDataColumnnames T
    ORDER BY ID
    SET @SQL = @SQL + ')'
    --PRINT 'TableCreate SQL=' + @SQL
    EXEC sp_executesql  @SQL

    -- insert data into output table
    SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT '
    SELECT @SQL = @SQL + CONCAT(CHAR(13)
        , CASE T.ID WHEN 1 THEN ' ' ELSE ',' END
        , ' t.n.value(''col[', T.ID, ']'', ''VARCHAR(4000)'') AS TheCol', T.ID)
    FROM #RawDataColumnnames T
    ORDER BY ID
    SET @SQL = @SQL + CONCAT(CHAR(13), 'FROM @TheXml.nodes(''/rows/row[@first="0"]/cols'') as t(n)')
    --PRINT 'Insert SQL=' + @SQL
    SET @ParamDef = N'@TheXml XML'
    EXEC sp_ExecuteSql  @SQL, @ParamDef, @TheXml=@MultilinesXml

GO

Example conversion (note that spaces are tabs!):

    EXEC dbo.uspDumpMultilinesWithHeaderIntoTable 'Deleteme', 'Left Centre  Right
A   B   C
D   E   F
G   H   I'

into (via 'SELECT * FROM deleteme')

Left    Centre  Right
A   B   C
D   E   F
G   H   I

Note that this is pragmatic code, written not as an efficiency exercise but to get a job done.

EDIT # Improved code (empty column name work-around, adds primary key)

    IF OBJECT_ID('dbo.uspDumpMultilinesWithHeaderIntoTable', 'P') IS NOT NULL DROP PROCEDURE dbo.uspDumpMultilinesWithHeaderIntoTable; 
GO
CREATE PROCEDURE dbo.uspDumpMultilinesWithHeaderIntoTable @TableName VARCHAR(127), @Multilines VARCHAR(MAX), @ColDelimDefault VARCHAR(9) = NULL, @Debug BIT = NULL
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#RawData') IS NOT NULL DROP TABLE #RawData
IF OBJECT_ID('tempdb..#RawDataColumnnames') IS NOT NULL DROP TABLE #RawDataColumnnames
DECLARE @Msg VARCHAR(4000)
DECLARE @PosCr INT, @PosNl INT, @TypeRowDelim VARCHAR(20)

-- work out type of row delimiter(s)
SET @PosCr = CHARINDEX(CHAR(13), @Multilines)
SET @PosNl = CHARINDEX(CHAR(10), @Multilines)
SET @TypeRowDelim = CASE
    WHEN @PosCr = @PosNl + 1 THEN 'NL_CR'
    WHEN @PosCr = @PosNl - 1 THEN 'CR_NL'
    WHEN @PosCr = 0 AND @PosNl > 0 THEN 'NL'
    WHEN @PosCr > 0 AND @PosNl = 0 THEN 'CR'
    ELSE CONCAT('? CR@', @PosCr, ', NL@', @PosNl, ' is unexpected') END

-- CR(x0d) is a 'good' row delimiter - make the data fit
DECLARE @RowDelim VARCHAR(9)

DECLARE @MultilinesSafe VARCHAR(MAX)
IF @TypeRowDelim = 'CR_NL' OR @TypeRowDelim = 'NL_CR' BEGIN
    SET @RowDelim = '
'
    SET @MultilinesSafe = REPLACE(@Multilines, CHAR(10), '')    -- strip LF
    SET @MultilinesSafe = (SELECT @MultilinesSafe FOR XML PATH(''))  -- escape any XML confusion
END 
ELSE IF @TypeRowDelim = 'CR' BEGIN
    SET @RowDelim = '
'
    SET @MultilinesSafe = @Multilines
    SET @MultilinesSafe = (SELECT @MultilinesSafe FOR XML PATH(''))  -- escape any XML confusion
END
ELSE IF @TypeRowDelim = 'NL' BEGIN
    SET @RowDelim = '
'
    SET @MultilinesSafe = REPLACE(@Multilines, CHAR(10), CHAR(13))  -- change LF to CR
    SET @MultilinesSafe = (SELECT @MultilinesSafe FOR XML PATH(''))  -- escape any XML confusion
END
ELSE
    RAISERROR(@TypeRowDelim , 10, 10)

DECLARE @ColDelim VARCHAR(9) = COALESCE(@ColDelimDefault, CHAR(9))
DECLARE @MultilinesXml XML
DECLARE @ColumnNamesAsString VARCHAR(4000)
DECLARE @SQL NVARCHAR(4000), @ParamDef NVARCHAR(4000)

IF @Debug = 1 BEGIN
    SET @Msg = CONCAT('TN=<', @TableName, '>, TypeRowDelim=<', @TypeRowDelim, '>, RowDelim(XML)=<', @RowDelim, '>, ColDelim=<', @ColDelim, '>, LEN(@Multilines)=', LEN(@Multilines))
    PRINT @Msg
END

SET @MultilinesSafe = '<rows><row first="1"><cols><col first="1">' + REPLACE(REPLACE(@MultilinesSafe, @RowDelim, '</col></cols></row><row first="0"><cols><col first="0">'), @ColDelim, '</col><col>') + '</col></cols></row></rows>'
SET @MultilinesXml = @MultilinesSafe
--IF @Debug = 1 PRINT CAST(@MultilinesXml AS VARCHAR(MAX))

-- extract Column names
SELECT
    IDENTITY(INT, 1, 1) AS ID,
    t.n.query('.').value('.', 'VARCHAR(4000)') AS ColName
INTO #RawDataColumnnames
FROM @MultilinesXml.nodes('/rows/row[@first="1"]/cols/col') AS t(n) -- just first row
ALTER TABLE #RawDataColumnnames ADD CONSTRAINT [PK_#RawDataColumnnames] PRIMARY KEY CLUSTERED(ID)
-- now tidy any strange characters in column name
UPDATE T SET ColName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ColName, '.', '_'), ' ', '_'), '[', ''), ']', ''), '.', ''), '$', '') FROM #RawDataColumnnames T
-- now fix any empty column names
UPDATE T SET ColName = CONCAT('_Col_', ID, '_') FROM #RawDataColumnnames T WHERE ColName = ''

IF @Debug = 1 BEGIN
    SET @Msg = CONCAT('#Cols(FromHdr)=', (SELECT COUNT(*) FROM #RawDataColumnnames) )
    PRINT @Msg
END

-- create output table
SET @SQL = 'IF OBJECT_ID(''' + @TableName + ''') IS NOT NULL DROP TABLE ' + @TableName
--PRINT 'TableDelete SQL=' + @SQL
EXEC sp_executesql  @SQL

SET @SQL = 'CREATE TABLE ' + @TableName + '('

SET @SQL = @SQL + '[_Row_PK_] INT IDENTITY(1,1) PRIMARY KEY,'   -- PK

SELECT @SQL = @SQL + CASE T.ID WHEN 1 THEN '' ELSE ', ' END
    + CHAR(13) + '['+ T.ColName + '] VARCHAR(4000) NULL'
FROM #RawDataColumnnames T
ORDER BY ID

SET @SQL = @SQL + ')'
--PRINT 'TableCreate SQL=' + @SQL
EXEC sp_executesql  @SQL

-- insert data into output table
SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT '
SELECT @SQL = @SQL + CONCAT(CHAR(13)
    , CASE T.ID WHEN 1 THEN ' ' ELSE ',' END
    , ' t.n.value(''col[', T.ID, ']'', ''VARCHAR(4000)'') AS TheCol', T.ID)
FROM #RawDataColumnnames T
ORDER BY ID
SET @SQL = @SQL + CONCAT(CHAR(13), 'FROM @TheXml.nodes(''/rows/row[@first="0"]/cols'') as t(n)')
--PRINT 'Insert SQL=' + @SQL
SET @ParamDef = N'@TheXml XML'
EXEC sp_ExecuteSql  @SQL, @ParamDef, @TheXml=@MultilinesXml

GO

Running this with

    EXEC dbo.uspDumpMultilinesWithHeaderIntoTable  'Deleteme', 'Left        Right
A   B   C
D   E   F
G   H   I'

Results in

_Row_PK_    Left    _Col_2_ Right
1   A   B   C
2   D   E   F
3   G   H   I