在SQL Server中，将字符串按行分隔符和列分隔符拆分为表。

Question

在SQL Server中，将字符串按行分隔符和列分隔符拆分为表。

10

如何在SQL Server中将包含矩阵的字符串分割成表格？字符串具有列和行分隔符。

假设我有一个字符串：

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

预期结果（分成三列）：

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

我正在寻找一种通用解决方案，它没有定义列数和行数。因此字符串为：

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

will be split into

table with two columns:

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

我的努力。 我的第一个想法是使用动态SQL，将字符串转换为：insert into dbo.temp values (...)。虽然这种方法非常快，但有一个小缺点，因为它需要先创建具有正确列数的表格。我在下面的问题答案中提供了这种方法，只是为了让问题简短明了。

另一个想法是将字符串写入服务器上的CSV文件，然后使用bulk insert导入。虽然我不知道如何做以及第一和第二个想法的性能如何。

我提出这个问题的原因是因为我想从Excel导入数据到SQL Server。由于我尝试了不同的ADO方法，所以当字符串长度增加时，通过发送矩阵字符串的方法是压倒性的胜利。我在这里问了一个相似的问题：将Excel范围转换为VBA字符串，其中您可以找到有关如何从Excel范围准备此类字符串的建议。

悬赏我决定授予Matt。 我高度评价Sean Lange的回答。谢谢Sean。我喜欢Matt的答案因为它简单而简洁。除了Matt和Sean之外的不同方法可以并行使用，因此目前我不接受任何答案（更新：几个月后，我接受了Matt的答案）。我感谢Ahmed Saeed提出的使用VALUES的想法，因为这是我开始的答案的一个很好的演变。当然，它无法与Matt或Sean匹敌。我投票支持每个答案。我将感激您对使用这些方法的任何反馈。感谢您的关注。

- Przemyslaw Remin

踩负评也可能是由于没有展示他所尝试的内容。 - pabrams

你可以假设最大列数为100，并编写一个CLR TVF来拆分字符串，例如：基于http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx - Martin Smith

我希望能够联系到一些广受尊敬的SQL专家，他们可能会分享对提供答案的看法。你们中的一些答案超出了我的当前知识水平。在赏金期限只剩下几天的情况下，我可能无法在短时间内完全理解和消化它们。因此，我恳请任何声誉高的人士，请就这些答案发表评论。帮助我公正地颁发奖励。 - Przemyslaw Remin

只是为了明确一下...您需要将矩阵插入到现有表格中，还是仅像表格/网格一样显示它？ - Susilo

@Susilo 是的，我需要将它插入到另一个表中。但是像在创建表时使用 select * into table 的方式插入会比 insert into table select * ... 更方便，因为第一种方式更加便捷。请参见我的回答中的进一步说明。 - Przemyslaw Remin

在你的回答中，将矩阵数据插入到临时表中...这是必要的吗？而且这已经是一个很好的解决方案了。但真正需要实现什么呢？动态临时表还是？ - Susilo

12个回答

5

其中比较简单的方法是将字符串转换为XML，基于替换您的分隔符。

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)

将格式化字符串转换为XML，基本上需要添加开始和结束标记，然后用列标记替换列分隔符，用行和列标记替换行分隔符。
.nodes 是xml数据类型的方法，"当您想将xml数据类型实例解析为关系数据时非常有用" https://msdn.microsoft.com/en-us/library/ms188282.aspx
as t(n) 告诉您如何访问XML行和列。t是表别名，n是节点别名（类似于行）。因此，t.n.value()获取特定行
COL[1] 表示获取行中第一个COL标记，它是基于1的，所以2是下一个，然后是3等等。
CHAR(1) 是数据类型定义，表示1个字符，基于您的示例数据每列只有1个字符。您可能会注意到我在动态查询中将其设置为VARCHAR(MAX)，因为如果数据类型未知，则需要更多灵活性。

或者动态地：

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

- Matt

我正在寻找一个通用解决方案。我可以使用NVARCHAR(max)替代CHAR(1)吗？ - Przemyslaw Remin

@Matt 我在这里发布了一个新答案，你可能会觉得很有趣。 - Sean Lange

@SeanLange 看起来不错，我很好奇在一些答案上看到性能差异。Siyual 在他的答案中也使用了 split split cross 技术，但是你的分割函数差异巨大，而他选择了枢轴，而你则选择了带有计数表的条件聚合。稍后我会运行代码并测试其中的部分，以确保我完全理解技术。但是好答案+1，很好地利用了支持文章和文档的基础集合分割器。 - Matt

@Matt 老实说，只有两个答案值得测试。你的和我的。Siyual 的那个使用了循环拆分器，我不会费力在那里测试性能，即使是中等大小的数据它也会输。另外，Shrikant 的那个创建了一个永久表，这意味着它仅能执行一次，除非你也删除该表，但是为此创建和删除表也有点无意义。如果我能找到一些时间，我将采用你的动态解决方案和我的方法进行测试。 - Sean Lange

1

@PrzemyslawRemin @SQL 查询我们生成，@ParameterDef 这个变量包含了我们需要在查询中使用的变量定义。最后一部分是将 @xmlstr 作为在 @ParameterDef 中定义的变量传递到 sp_executesql 中。 - gofr1

显示剩余8条评论

3

以下代码应该适用于SQL Server。它使用公共表达式和动态SQL进行一些操作。只需将字符串值分配给@str变量，并一次性执行完整的代码即可。由于它使用了CTE，因此很容易在每个步骤中分析数据。

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);

以下是来自http://statisticsparser.com/的上述查询的统计信息截图。

- Ajay Dwivedi

2

**--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)

- Shrikant Kumbhar

这个可以运行，但效率低下。对于这种情况，没有必要使用循环或动态SQL。 - Sean Lange

@SeanLange，如果列数未知，您说不需要动态SQL是什么意思？您如何建议在不动态构建查询的情况下完成它？此外，他的答案和我的动态答案的好处在于循环仅用于列定义，而不是数据操作。 - Matt

@SeanLange 把动态交叉方法作为答案提供，我很想看看你的想法，并为人们提供更多选择。我发现 XML 方法可能是编码最简单的方法，但 XML 性能并不总是很好。我非常怀疑仅用于列定义的循环会对解决方案产生太多开销...笛卡尔积可能会造成一些压力...我也讨厌循环，通常情况下我们可以改变如何定义我们的列到一个递归 CTE，然后连接行，认为循环可能会更快速地完成这个轻量级使用... - Matt

1

同意。当递归CTE未被正确使用时，它们确实会消耗服务器的资源。我有一些实际工作需要处理，但我一定会回来解决这个问题，因为我觉得这是一个有趣的挑战。 - Sean Lange

@SeanLange .. 抱歉...但我是 Stack Overflow 的新手..而且我把问题当成了游戏.. :-) - Shrikant Kumbhar

@ShrikantKumbhar 这实际上是一个好答案，不是我想到的。我只会使用临时表[tt]来增强并测试tt的存在性。但这确实是一种非常稳定的技术，特别是如果OP想要将数据保存超过1个操作，可能是最好的技术。 - Matt

2

我将我的答案发布出来只是为了扩展问题，展示我在提问时所使用的内容。

思路是将原始字符串改变为：

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

以下是存储过程示例：

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

使用以下代码执行此存储过程：

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

生成以下@String：

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

这个过程的最后一行是动态执行的。

解决方案需要首先创建适当的dbo.table，然后将值插入其中。这是一个小缺点。因此，如果有一个结构：select * into dbo.temp，那么这个解决方案就不是那么动态了。尽管如此，我还是想分享这个解决方案，因为它有效、快速、简单，也许会给其他答案提供灵感。

- Przemyslaw Remin

2

这里有另一种方法。

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

返回

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

现在，这需要一个解析器，其列表如下：

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',' ')

举个例子，第一次解析将返回

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

然后通过 CROSS APPLY 再次解析，返回以下内容并存储在临时表中。

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

编辑：或者只是为了好玩

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)

返回

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

- John Cappelletti

2

这个问题可以在不需要临时表、视图、循环或xml的情况下解决。首先，您可以根据下面的示例创建一个基于计数表的字符串拆分函数：

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;

首先使用分割函数根据行分隔符拆分字符串。然后在每行上再次使用分割函数，使用OUTER APPLY语句。最后进行数据透视。由于列数未知，查询必须作为动态SQL执行，如下所示：

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.

- Edmond Quinton

2

一些包含数据透视和动态SQL的XML。

用标签 p 和 row 替换 , 和 ;，将其转换为 XML 格式，
然后计算列数并将其放入 @i 中，
使用 CTE 的 colsPiv 生成一个字符串并将其放入 @col 中，该字符串类似于 ,[1],[2],..[n]，它将用于数据透视，
接着我们生成一个动态的数据透视查询，并执行它。我们还传递了两个参数：XML 和列数。

这是查询语句：

--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

A,B,C;D,E,F;X,Y,Z的输出结果为:

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

对于A,B;D,E;X,Y：

1   2
A   B
D   E
X   Y

- gofr1

关于创建XML字符串，您的解决方案与Matt的解决方案有何不同？ - Przemyslaw Remin

1

说实话，我滚动并阅读了所有提供的答案 - 然后写了自己的答案 :) 所有答案都使用了类似但不同的方法。Matt 使用 while 循环构建查询，并直接从 XML 中获取列，我使用了数据透视表，并生成了没有循环的查询。Siyual 使用了分割函数，John Cappelletti 使用了带有 XML 和交叉应用的函数，Edmond Quinton 使用了带有 XML 和数据透视表的分割函数。我不喜欢使用函数/while 循环/临时表，这就是为什么我的答案能够胜任的原因！ - gofr1

在更加熟悉 SQL 中的数据透视表之后，我确实欣赏您的答案的优雅。 - Przemyslaw Remin

2

在这个解决方案中，我将尽可能多地使用字符串操作。该过程将通过将输入字符串转换为适合VALUES关键字的形式来构建动态SQL语句，通过计算列数来构建列标题并生成所需的标题。然后，只需执行构造的SQL语句即可。

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', ';', ','

方法二：

为了解决PrzemyslawRemin所指示的1000行值限制问题，这里进行了一些小修改，将输入字符串转换为一个行xml字段，然后使用CROSS APPLY与其各个元素一起交叉应用。

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', ';', ','

- Ahmed Saeed

这是一个有趣的想法。在 select * from VALUES(...) 子句中是否需要 as T(...)？你的尝试中列名是否是必需的？ - Przemyslaw Remin

一个 VALUES 子句中的最大行数为 1000。因此，对于更大的数据集，这个解决方案需要进行一些修改。 - Przemyslaw Remin

@PrzemyslawRemin，“select from”需要一个表别名和列名。 - Ahmed Saeed

没错，最多可以有1000条记录，我之前不知道，谢谢。我猜对于一个包含在字符串变量中的表来说，这应该足够了。但是，可以使用多个插入或修改代码。 - Ahmed Saeed

1

这里有一种通过动态PIVOT和使用Split自定义函数的方法来实现此操作： Split函数

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

- Siyual

3

我希望人们不要再发这些基于循环的分割器了，它们的性能太差了。有许多基于集合的分割器可以大幅超越这些基于行的分割器。更糟糕的是，还有一种特别慢的多语句表值函数。这种函数比标量函数还要慢。你应该阅读这篇文章，并放弃使用那个慢速循环函数。http://sqlperformance.com/2012/07/t-sql-queries/split-strings - Sean Lange

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Sean Lange · Accepted Answer

好的，这个谜题引起了我的兴趣，所以我决定尝试在没有任何循环的情况下完成它。这需要一些前提条件才能实现。首先，我们假设您拥有某种形式的计数表。如果您没有，请使用以下代码创建一个。我将此保存在我使用的每个系统上。

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

这个难题的第二个部分是需要一个基于集合的字符串拆分器。我偏爱超级快速的Jeff Moden拆分器。唯一的注意点是它只能处理长度不超过8000的varchar值。对于我处理的大多数分隔符字符串来说，这已经足够了。你可以在这里找到Jeff Moden的拆分器（DelimitedSplit8K）。

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

最后但同样重要的是，我在这里使用的技术是动态交叉表。这是我从Jeff Moden那里学到的另一种技术。他在这个主题上有一篇很棒的文章。

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

将所有这些内容结合起来，你可以得到像这样的东西，它将非常快速并且能够很好地扩展。

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

--编辑--

如果链接失效，这里是DelimitedSplit8K函数。

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
;