如何用SQL将SQL Server中的发票表行按指定列拆分为多行？

需求说明 #

已将CSV文件导入Azure Data Studio和SSMS中的InvoicesDemo2表，表中包含表头行（Column1为"H"）和明细行（Column1为"D"）。需要将表中每行数据按第8列（Column8）拆分为多行，并将拆分后的数据插入到原数据下方。

适用环境 #

SQL Server 2016及以上版本（内置STRING_SPLIT函数），或所有SQL Server版本（使用自定义拆分函数）

解决方案代码 #

方法一：使用内置STRING_SPLIT函数（SQL Server 2016+） #

适用于Column8使用单一分隔符（如逗号、分号）分隔多个值的场景：

-- 将Column8拆分后的数据插入原表
INSERT INTO InvoicesDemo2
(
    Column1, Column2, Column3, Column4, Column5,
    Column6, Column7, Column8, Column9, Column10
)
SELECT 
    Column1, Column2, Column3, Column4, Column5,
    Column6, Column7, value AS Column8, Column9, Column10
FROM InvoicesDemo2
CROSS APPLY STRING_SPLIT(Column8, ',') -- 替换','为实际使用的分隔符

方法二：使用自定义拆分函数（兼容所有SQL Server版本） #

如果SQL Server版本低于2016，先创建自定义拆分函数再执行插入：

-- 创建自定义字符串拆分函数
CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter NVARCHAR(50)
)
RETURNS @Result TABLE(Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @Xml XML
    SET @Xml = N'<root><r>' + REPLACE(@String, @Delimiter, '</r><r>') + '</r></root>'
    
    INSERT INTO @Result(Value)
    SELECT r.value('.', 'NVARCHAR(MAX)')
    FROM @Xml.nodes('//root/r') AS RECORDS(r)
    
    RETURN
END
GO

-- 使用自定义函数拆分并插入数据
INSERT INTO InvoicesDemo2
(
    Column1, Column2, Column3, Column4, Column5,
    Column6, Column7, Column8, Column9, Column10
)
SELECT 
    Column1, Column2, Column3, Column4, Column5,
    Column6, Column7, value AS Column8, Column9, Column10
FROM InvoicesDemo2
CROSS APPLY dbo.SplitString(Column8, ',') -- 替换','为实际分隔符

验证结果 #

执行插入后，可通过以下语句查看原数据和拆分后的数据（按原发票编号排序，方便对比）：

SELECT * FROM InvoicesDemo2 ORDER BY Column3, Column8

内容的提问来源于stack exchange，提问作者Liam Grist