sql - 如何在 SQL 中使用 CAST 在一列/原始中显示多个“GL 编码”

Question

   ; 
    WITH documentswith42cols 
         AS (SELECT document_id 
             FROM   documentmetadata 
             GROUP  BY document_id 
             HAVING Count(1) = 42), 
         test 
         AS (SELECT DM.document_id, 
                    wf.id, 
                    wf.currentstatename, 
                    DM.displayname, 
                    F.NAME, 
                    DM.fieldvalue 
             FROM   documentmetadata DM 
                    --inner join DocumentsWith42Cols cols42 on cols42.Document_ID = DM.Document_ID -- ignore incase of less than 42 columns 
                    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)-- specific document id's 
             UNION ALL 
             SELECT DM.document_id, 
                    wf.id, 
                    wf.currentstatename, 
                    DM.displayname, 
                    F.NAME, 
            **Cast(DM.fieldvalue AS XML).value('(/DocumentElement//TableFieldColumn/Account_x0020_Number/node())[1]', 'varchar(max)') + ' , ' +
            Cast(DM.fieldvalue AS XML).value('(/DocumentElement//TableFieldColumn/Account_x0020_Number/node())[2]', 'varchar(max)')** 

            AS FieldValue 
            FROM   documentmetadata DM 

           --inner join DocumentsWith42Cols cols42 on cols42.Document_ID = DM.Document_ID -- ignore incase of less than 42 columns 
           INNER JOIN field F 
                   ON DM.field_id = F.id 
                      AND f.NAME = 'GL Coding' 
                      AND F.id = 331 -- Duplicate 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  wf.document_id IN ( 20113 )-- specific document id's 
            ) `enter code here`
    SELECT [GL Coding] 
    FROM   (SELECT document_id, 
                   id, 
                   NAME, 
                   fieldvalue 
            FROM   test) AS SourceTable 
           PIVOT (Max(fieldvalue) 
                 FOR NAME IN ([GL Coding])) AS pivottable 

请帮助我使用 CAST 在一个原始/列中显示 GL 编码的多个值。因为我不知道如何使用 CAST。现在我为你可以在 BOLD 中看到的 2 值做，但如果它大于 1，我需要显示所有值。

[您可以在这张图片中查看我有两个 GL 代码，因为我在查询中为 2 编写了代码][1] [1]：https ://i.stack.imgur.com/d8ymm.jpg

标签： sqlsql-serversql-server-2008