如何修改SQL查询实现NULL行数据拼接至下一行并移除原行

现有查询及结果 #

现有用于填充空列的SQL查询：

SELECT *,
    ModifiedLineData = CASE
        WHEN Column2 = '' AND LineData NOT LIKE ',,,0,,,,0'
            THEN CONCAT(STUFF(LineData, CHARINDEX(',', LineData, CHARINDEX(',', LineData) + 1), 0, '"No PO Number"'), ',""')
        ELSE CONCAT(LineData, ',""')
    END
FROM (
    SELECT
        *,
        Column2 = CONVERT(XML, '<s>' + REPLACE((SELECT ISNULL(LineData, '') FOR XML PATH('')), ',', '</s><s>') + '</s>').value('/s[2]', 'varchar(100)')
    FROM [dbo].[Temp_Raw_Data]
    WHERE LineData NOT LIKE ',,,0,,,,0'
) AS Subquery

执行后返回结果：

需求说明 #

• 当Column2为NULL时，将该行的LineData拼接至下一行的ModifiedLineData末尾
• 当Column2不为NULL时，仅在ModifiedLineData后拼接,""
• 最终结果移除Column2为NULL的行
• 已知数据特性：不会出现连续的NULL行

期望结果：

尝试过的查询（未达预期） #

SELECT * 
  FROM (SELECT FileName, LineNumber, LineData, Column2, 
               CASE WHEN LAG(Column2) OVER(ORDER BY LineNumber) IS NULL
                    THEN CONCAT_WS(', ',
                                ModifiedLineData, 
                                LAG(ModifiedLineData) OVER(ORDER BY LineNumber))
                    ELSE ModifiedLineData
               END AS ModifiedLineData
        FROM (
        SELECT *,
            ModifiedLineData = CASE
                WHEN Column2 = '' AND LineData NOT LIKE ',,,0,,,,0'
                    THEN CONCAT(STUFF(LineData, CHARINDEX(',', LineData, CHARINDEX(',', LineData) + 1), 0, '"No PO Number"'), '')
                ELSE CONCAT(LineData, '')
            END
        FROM (
            SELECT *,
                Column2 = CONVERT(XML, '<s>' + REPLACE((SELECT ISNULL(LineData, '') FOR XML PATH('')), ',', '</s><s>') + '</s>').value('/s[2]', 'varchar(100)')
            FROM [backstreet_WMS_Optimizer].[dbo].[Temp_GoodsIn_Raw_Data]
            WHERE LineData NOT LIKE ',,,0,,,,0'
        ) AS Subquery
    ) AS cte
) AS Subquery
WHERE Column2 IS NOT NULL
order by FileName, LineNumber

解决方案 #

核心思路：利用LEAD()函数获取当前行的下一行数据，判断下一行的Column2是否为NULL，若是则将其LineData拼接到当前行的ModifiedLineData；否则拼接,""。最后过滤掉Column2为NULL的行。

SELECT 
    FileName,
    LineNumber,
    LineData,
    Column2,
    ModifiedLineData = CASE
        -- 检查下一行的Column2是否为NULL，若是则拼接下一行的LineData
        WHEN LEAD(Column2) OVER(ORDER BY LineNumber) IS NULL
            THEN CONCAT(
                -- 原逻辑生成的基础ModifiedLineData（去掉原末尾的,""，因为要替换成目标内容）
                CASE 
                    WHEN Column2 = '' AND LineData NOT LIKE ',,,0,,,,0'
                        THEN STUFF(LineData, CHARINDEX(',', LineData, CHARINDEX(',', LineData) + 1), 0, '"No PO Number"')
                    ELSE LineData
                END,
                ',',
                LEAD(LineData) OVER(ORDER BY LineNumber)
            )
        -- 下一行不是NULL，按需求拼接,""
        ELSE CONCAT(
            CASE 
                WHEN Column2 = '' AND LineData NOT LIKE ',,,0,,,,0'
                    THEN STUFF(LineData, CHARINDEX(',', LineData, CHARINDEX(',', LineData) + 1), 0, '"No PO Number"')
                ELSE LineData
            END,
            ',""'
        )
    END
FROM (
    SELECT
        *,
        Column2 = CONVERT(XML, '<s>' + REPLACE((SELECT ISNULL(LineData, '') FOR XML PATH('')), ',', '</s><s>') + '</s>').value('/s[2]', 'varchar(100)')
    FROM [dbo].[Temp_Raw_Data]
    WHERE LineData NOT LIKE ',,,0,,,,0'
) AS Subquery
-- 过滤掉Column2为NULL的行
WHERE Column2 IS NOT NULL
ORDER BY FileName, LineNumber;

逻辑说明 #

1. 内层子查询保持原有的Column2解析逻辑不变
2. 外层使用LEAD(Column2) OVER(ORDER BY LineNumber)判断当前行的下一行是否为NULL行
3. 若下一行是NULL行，就将当前行生成的基础ModifiedLineData与下一行的LineData拼接；否则拼接,""
4. 最后过滤掉Column2为NULL的行，得到预期结果

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