如何使用SQL的CAST函数将多个GL Coding值显示在同一列/行

我明白你的痛点——手动指定[1]、[2]来提取账号太死板，没法应对动态数量的GL Coding条目。下面我会帮你优化SQL代码，实现自动抓取所有匹配的Account Number并拼接成同一列的字符串。

核心思路 #

原代码里用CAST(DM.fieldvalue AS XML).value(...)只能逐个提取单个节点，我们需要先把XML中的所有Account_x0020_Number节点拆分成单独的行，再用聚合函数把这些行拼接成一个逗号分隔的字符串。

优化后的代码（SQL Server 2017及以上版本） #

这个版本用STRING_AGG函数，语法更简洁直观：

; WITH documentswith42cols AS (
    SELECT document_id 
    FROM documentmetadata 
    GROUP BY document_id 
    HAVING Count(1) = 42
), 
gl_coding_processed AS (
    -- 专门处理GL Coding字段，提取所有Account Number并拼接
    SELECT 
        DM.document_id,
        wf.id,
        wf.currentstatename,
        DM.displayname,
        F.NAME,
        -- 用STRING_AGG拼接所有Account Number
        STRING_AGG(account_num.value('.', 'varchar(max)'), ' , ') AS FieldValue
    FROM documentmetadata DM
    INNER JOIN field F ON DM.field_id = F.id AND f.NAME = 'GL Coding' AND F.id = 331
    INNER JOIN workflowitem wf ON wf.document_id = dm.document_id 
        AND Isnull(wf.isrunning, 1) = 1 
        AND Isnull(wf.isterminated, 0) = 0
    -- 拆解XML中的所有Account_x0020_Number节点
    CROSS APPLY CAST(DM.fieldvalue AS XML).nodes('/DocumentElement//TableFieldColumn/Account_x0020_Number') AS t(account_num)
    WHERE wf.document_id IN (20113)
    GROUP BY DM.document_id, wf.id, wf.currentstatename, DM.displayname, F.NAME
),
test AS (
    -- 非GL Coding字段的原有逻辑
    SELECT 
        DM.document_id, 
        wf.id, 
        wf.currentstatename, 
        DM.displayname, 
        F.NAME, 
        DM.fieldvalue 
    FROM documentmetadata DM
    INNER JOIN field F ON DM.field_id = F.id AND f.NAME <> 'GL Coding'
    INNER JOIN workflowitem wf ON wf.document_id = dm.document_id 
        AND Isnull(wf.isrunning, 1) = 1 
        AND Isnull(wf.isterminated, 0) = 0
    WHERE DM.document_id IN (20113)
    
    UNION ALL
    
    -- 合并处理后的GL Coding字段
    SELECT document_id, id, currentstatename, displayname, NAME, FieldValue
    FROM gl_coding_processed
)
SELECT [GL Coding] 
FROM (
    SELECT document_id, id, NAME, fieldvalue 
    FROM test
) AS SourceTable 
PIVOT (
    Max(fieldvalue) FOR NAME IN ([GL Coding])
) AS pivottable

兼容旧版本SQL Server（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用XML PATH + STUFF的方式实现拼接：

; WITH documentswith42cols AS (
    SELECT document_id 
    FROM documentmetadata 
    GROUP BY document_id 
    HAVING Count(1) = 42
), 
gl_coding_processed AS (
    SELECT 
        DM.document_id,
        wf.id,
        wf.currentstatename,
        DM.displayname,
        F.NAME,
        -- 用XML PATH拼接所有Account Number，STUFF去掉开头的逗号
        STUFF(
            (SELECT ' , ' + account_num.value('.', 'varchar(max)')
             FROM CAST(DM.fieldvalue AS XML).nodes('/DocumentElement//TableFieldColumn/Account_x0020_Number') AS t(account_num)
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),
            1, 3, ''
        ) AS FieldValue
    FROM documentmetadata DM
    INNER JOIN field F ON DM.field_id = F.id AND f.NAME = 'GL Coding' AND F.id = 331
    INNER JOIN workflowitem wf ON wf.document_id = dm.document_id 
        AND Isnull(wf.isrunning, 1) = 1 
        AND Isnull(wf.isterminated, 0) = 0
    WHERE wf.document_id IN (20113)
    GROUP BY DM.document_id, wf.id, wf.currentstatename, DM.displayname, F.NAME, DM.fieldvalue
),
test AS (
    -- 非GL Coding字段的原有逻辑
    SELECT 
        DM.document_id, 
        wf.id, 
        wf.currentstatename, 
        DM.displayname, 
        F.NAME, 
        DM.fieldvalue 
    FROM documentmetadata DM
    INNER JOIN field F ON DM.field_id = F.id AND f.NAME <> 'GL Coding'
    INNER JOIN workflowitem wf ON wf.document_id = dm.document_id 
        AND Isnull(wf.isrunning, 1) = 1 
        AND Isnull(wf.isterminated, 0) = 0
    WHERE DM.document_id IN (20113)
    
    UNION ALL
    
    -- 合并处理后的GL Coding字段
    SELECT document_id, id, currentstatename, displayname, NAME, FieldValue
    FROM gl_coding_processed
)
SELECT [GL Coding] 
FROM (
    SELECT document_id, id, NAME, fieldvalue 
    FROM test
) AS SourceTable 
PIVOT (
    Max(fieldvalue) FOR NAME IN ([GL Coding])
) AS pivottable

关键部分解释 #

1. CROSS APPLY ... nodes()：这个方法把XML中的所有Account_x0020_Number节点拆分成单独的行，不管有多少个账号，都会被提取出来。
2. STRING_AGG/XML PATH：负责把拆分后的账号行拼接成一个逗号分隔的字符串，自动处理所有数量的账号，不用手动指定索引。
3. 保持原有逻辑：非GL Coding字段的查询逻辑完全保留，只修改了GL Coding字段的处理部分，确保整体输出和你原来的需求一致。

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