在SQL Server中，使用分隔符将1列数据拆分为多列

Question

以下是我的记录

13;16;AB BC  13;16;BC CD  13;16;CD DE

我想要下面的结果

|  Column 1 | Column 2  |  column 3 | 
|-----------|-----------|-----------|
|  13       |        16 |    AB BC  | 
|  13       |        16 |    BC CD  |  
|  13       |        16 |    CD DE  |  

备注：在这里，我想用双倍空格拆分记录，然后使用(;)进行内部拆分。

虽然我这里有三列，但在实践中可能会更多。因此，解决方案需要灵活。

Answer 1

使用动态代码很好地使用Replace函数

DECLARE @X VARCHAR(100)='13;16;AB BC  13;16;BC CD  13;16;CD DE'
DECLARE @SQL VARCHAR(MAX)=''
SELECT @SQL = REPLACE( '
SELECT * FROM (
SELECT '''+REPLACE(@X,'  ',''' UNION ALL 
SELECT '''),';',''',''') + '''
)AS TAB(COL1,COL2,COL3)
'
EXEC(@SQL)

结果

COL1    COL2    COL3
13      16      AB BC
13      16      BC CD
13      16      CD DE

Answer 2

这不是一个理想的解决方案，并且根据OP对另一个答案的评论进行了一些猜测">这里的数据不会固定到3列，它可以超过3列"。

因此，我制作了自己的样本数据并进行了猜测。 请注意，这使用了 Delimitsplit8k，因此您将需要它才能使用此解决方案：

USE Sandbox;
GO
CREATE TABLE dbo.StringsTable (ID int IDENTITY(1,1), --No idea if you have this, but sort order might be important
SomeString varchar(8000));
--insert sample data
INSERT INTO dbo.StringsTable (SomeString)
VALUES ('13;16;AB BC'),
('13;16;BC CD'),
('13;16;CD DE');
--Some more data, the OP says that can have strings with more (and maybe less) than 3 delimiters, but "forgot" to supply such examples...
INSERT INTO dbo.StringsTable (SomeString)
VALUES ('10'),
('19;124;BC CD;29;ZY XY'),
('29;AB CF'),
('43;BE HG;GY TY;18');
GO
SELECT *
FROM dbo.StringsTable;
GO
DECLARE @SQL nvarchar(MAX);
--Get the maximum number of parts
WITH MaxParts AS(
SELECT MAX(DS.ItemNumber) AS MaxPart
FROM dbo.StringsTable ST
CROSS APPLY dbo.DelimitedSplit8K(ST.SomeString,';') DS),
--Need a tally table now
N AS(
SELECT *
FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) V(N)),
Tally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
FROM N N1 --10
CROSS JOIN N N2 --100
CROSS JOIN N N3 --1000
) --If you have more than 1000 parts then repeat, but remember, delimitedSplit8K expectes a varchar(8000), not MAX
--Of course, also fee free to lower the number the tally returns, if it's not needed
SELECT @SQL =
N'SELECT ' +
STUFF((SELECT N',' + NCHAR(10) + '       MAX(CASE DS.ItemNumber WHEN ' + QUOTENAME(T.i,N'''') + N' THEN DS.Item END) AS ' + QUOTENAME('Column' + CONVERT(nvarchar(4),T.i))
FROM Tally T
WHERE T.i <= (SELECT MaxPart FROM MaxParts MP)
ORDER BY t.i
FOR XML PATH(N'')),1,9,'') + NCHAR(10) +
N'FROM dbo.StringsTable ST' + NCHAR(10) +
N'     CROSS APPLY dbo.DelimitedSplit8K(ST.SomeString,'';'') DS' + NCHAR(10) +
N'GROUP BY ST.ID' + NCHAR(10) + 
N'ORDER BY ST.ID;'
PRINT @SQL; --your best friend
--Run the SQL
EXEC sp_executesql @SQL;
GO
DROP TABLE dbo.StringsTable;
GO