sql - STUFF FOR XML PATH - 将多行组合成一个单列单元格

Question

假设我在一个看起来像这样的表中有数据

   Num1        Type1        Type2       Acct        Ind
   ---         ----         ----        ---         ---
   1X2         XXX          CA          111         ERROR
   1X2         XXX          CA          222         ERROR

   X22         XXX          CA          111         ERROR
   X22         XXX          CA          222         ERROR
   X22         XXX          CA          333         ERROR
   X22         XXX          CA          444         ERROR

我想要实现的是添加一个新列，并最终实现如下所示：

   Num1        Type1        Type2       Acct        Ind         Acct_List
   ---         ----         ----        ---         ---         --------
   1X2         XXX          CA          111         ERROR       111,222
   1X2         XXX          CA          222         ERROR       111,222

   X22         XXX          CA          111         ERROR       111,222,333,444
   X22         XXX          CA          222         ERROR       111,222,333,444
   X22         XXX          CA          333         ERROR       111,222,333,444
   X22         XXX          CA          444         ERROR       111,222,333,444

我试图做这样的事情：

    SELECT Num1, Type1, Type2, Acct, Ind, 'Acct_List' = STUFF((SELECT ', ' + CAST(ACCT AS VARCHAR(55)) FROM Table FOR XML PATH (''),1,1,'')
    FROM Table
    WHERE IND='ERROR'
    GROUP BY Num1, Type1, Type2, IND

但是，使用此代码会发生什么，每一行都返回 ACCT_LIST 中的所有 ACCT

有没有办法让这个看起来像结果数据？

标签： sqlsql-serverssmsstuff

Answer 1

您缺少 where 子句：

SELECT Num1, Type1, Type2, Acct, Ind, 'Acct_List' = STUFF((SELECT ', ' + CAST(ACCT AS VARCHAR(55)) FROM Table t1 WHERE t1.Num1 = t2.Num1 FOR XML PATH (''),1,1,'')
FROM Table t2
WHERE IND='ERROR'
GROUP BY Num1, Type1, Type2, IND