如何在同一段SQL中，将拆分后的字段值写回原表同一条记录？

我使用以下脚本从DWNAME字段取值并拆分为10个独立值，现在需要将这10个值以查询语句中的别名作为列名，写回原表的同一条记录中。请问能否在同一段代码中完成该更新操作？

原查询脚本：

WITH DOCUMENT_ID AS
  (SELECT 
    DWDOCID, 
    VALUE, 
    ROW_NUMBER() OVER(partition by DWDOCID Order by DWDOCID) AS RowNum
  FROM [dwdata].[dbo].[SUAM]
    CROSS APPLY
        STRING_SPLIT(DWNAME,'^')
    WHERE DWDOCID > '3071822' AND DOCUMENT_TYPE IS NULL
    )

SELECT DWDOCID,
    [1] AS DRAWER,
    [2] AS DOCID,
    [3] AS STUDENTNUMBER,
    [4] AS STUDENTID,
    [5] AS LASTNAME,
    [6] AS FIRSTNAME,
    [7] AS FIELD5,
    [8] AS DOCTYPE,
    [9] AS CREATEDATE,
    [10] AS DOCUMENTYEAR
FROM DOCUMENT_ID
PIVOT
(MAX(VALUE)
FOR RowNum in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS PVT

可以在同一段代码中完成更新操作。通过CTE生成拆分并透视后的数据集，再关联原表执行更新即可，具体实现如下：

WITH DOCUMENT_ID AS
(
    SELECT 
        DWDOCID, 
        VALUE, 
        -- 注意：原排序逻辑无法保证拆分后的值顺序稳定，若使用SQL Server 2022+，建议用STRING_SPLIT(DWNAME,'^', 1)并按ordinal排序
        ROW_NUMBER() OVER(PARTITION BY DWDOCID ORDER BY (SELECT NULL)) AS RowNum
    FROM [dwdata].[dbo].[SUAM]
    CROSS APPLY STRING_SPLIT(DWNAME,'^')
    WHERE DWDOCID > '3071822' AND DOCUMENT_TYPE IS NULL
),
PIVOTED_DATA AS
(
    SELECT 
        DWDOCID,
        [1] AS DRAWER,
        [2] AS DOCID,
        [3] AS STUDENTNUMBER,
        [4] AS STUDENTID,
        [5] AS LASTNAME,
        [6] AS FIRSTNAME,
        [7] AS FIELD5,
        [8] AS DOCTYPE,
        [9] AS CREATEDATE,
        [10] AS DOCUMENTYEAR
    FROM DOCUMENT_ID
    PIVOT
    (
        MAX(VALUE)
        FOR RowNum IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
    ) AS PVT
)
UPDATE s
SET 
    s.DRAWER = pd.DRAWER,
    s.DOCID = pd.DOCID,
    s.STUDENTNUMBER = pd.STUDENTNUMBER,
    s.STUDENTID = pd.STUDENTID,
    s.LASTNAME = pd.LASTNAME,
    s.FIRSTNAME = pd.FIRSTNAME,
    s.FIELD5 = pd.FIELD5,
    s.DOCTYPE = pd.DOCTYPE,
    s.CREATEDATE = pd.CREATEDATE,
    s.DOCUMENTYEAR = pd.DOCUMENTYEAR
FROM [dwdata].[dbo].[SUAM] s
INNER JOIN PIVOTED_DATA pd ON s.DWDOCID = pd.DWDOCID
WHERE s.DWDOCID > '3071822' AND s.DOCUMENT_TYPE IS NULL;

关键说明 #

• 排序稳定性：原CTE中ORDER BY DWDOCID无法确保拆分后的值按原始字符串顺序排列。如果你的环境是SQL Server 2022及以上版本，建议改用STRING_SPLIT(DWNAME, '^', 1)（第三个参数启用序号返回），然后按ordinal列排序，这样能保证每个位置的值对应正确的目标列。
• 表结构要求：请确保原表SUAM已经存在DRAWER、DOCID等目标列，若不存在需要先执行ALTER TABLE语句添加这些列，否则更新会报错。

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