SQL Server单列数据转置：带表头CSV数据导入目标表

你的问题核心是没有区分表头行和数据行的角色，也没有通过位置关联数据值到对应的列，导致PIVOT逻辑错误。以下是修正后的方案：

前提说明 #

假设你的SplitOrdered_JSON函数返回两个字段：

• Value：拆分后的字符串内容
• OrderIndex：拆分后的顺序位置（从1开始）
  如果你的函数没有返回OrderIndex，可以用下面的定义替换（基于OpenJSON实现带顺序的拆分）：

CREATE OR ALTER FUNCTION dbo.SplitOrdered_JSON(@Input VARCHAR(MAX), @Delimiter VARCHAR(10))
RETURNS TABLE
AS
RETURN
(
    SELECT 
        Value = JSON_VALUE(j.value, '$'),
        OrderIndex = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM OPENJSON('["' + REPLACE(STRING_ESCAPE(@Input, 'json'), @Delimiter, '","') + '"]') j
)

完整解决方案代码 #

DROP TABLE IF EXISTS #Staging;
CREATE TABLE #Staging
(
    Data varchar(max)
)

INSERT INTO #Staging
SELECT 'Column1,Column3,Column4,Column5'
UNION ALL
SELECT 'A,,C,D'
UNION ALL
SELECT 'A,,,'
UNION ALL
SELECT 'A1,4,5,6'

DROP TABLE IF EXISTS #Final;
CREATE TABLE #Final
(
    Column1 varchar(100),
    Column2 varchar(100),
    Column3 varchar(100),
    Column4 varchar(100),
    Column5 varchar(100)
)

-- 核心处理逻辑
WITH StagingRows AS (
    -- 给每行加行号，区分表头行（RowNum=1）和数据行（RowNum>1）
    SELECT 
        Data,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM #Staging
),
HeaderMapping AS (
    -- 解析表头，得到【列名】和【对应的位置索引】
    SELECT 
        so.Value AS TargetColumn,
        so.OrderIndex AS ColumnPosition
    FROM StagingRows
    CROSS APPLY dbo.SplitOrdered_JSON(Data, ',') so
    WHERE RowNum = 1
),
DataValues AS (
    -- 解析所有数据行，得到【行号】、【数据值】和【所在位置索引】
    SELECT 
        sr.RowNum,
        so.Value AS CellValue,
        so.OrderIndex AS DataPosition
    FROM StagingRows sr
    CROSS APPLY dbo.SplitOrdered_JSON(sr.Data, ',') so
    WHERE sr.RowNum > 1
)
-- 关联表头和数据，PIVOT成目标表结构，插入#Final
INSERT INTO #Final (Column1, Column2, Column3, Column4, Column5)
SELECT
    ISNULL(pvt.Column1, '') AS Column1,
    NULL AS Column2, -- 表头无此列，所有行填充NULL
    ISNULL(pvt.Column3, '') AS Column3,
    ISNULL(pvt.Column4, '') AS Column4,
    ISNULL(pvt.Column5, '') AS Column5
FROM (
    SELECT 
        dv.RowNum,
        hm.TargetColumn,
        dv.CellValue
    FROM DataValues dv
    LEFT JOIN HeaderMapping hm ON dv.DataPosition = hm.ColumnPosition
) src
PIVOT (
    MAX(src.CellValue)
    FOR src.TargetColumn IN ([Column1], [Column3], [Column4], [Column5])
) pvt;

-- 验证结果
SELECT * FROM #Final;

逻辑说明 #

1. 标记行号：给临时表的每行添加行号，明确区分表头行和数据行。
2. 解析表头映射：拆分表头行，得到每个目标列（如Column1、Column3）对应的位置索引，建立「位置-列名」的映射关系。
3. 解析数据行：拆分所有数据行，记录每个数据值所在的位置索引和所属行号。
4. 关联与PIVOT：通过位置索引将数据值关联到对应的列名，再用PIVOT将行转列，同时对目标表中不存在于表头的列（如Column2）直接填充NULL。
5. 插入目标表：将处理后的结果插入#Final表，完成数据导入。

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