需要帮助将一列旋转到SQL Server 2012中的行 - 没有聚合，也没有标签列存在[重复]

This question already has an answer here:

这个问题在这里已有答案：

Simulating group_concat MySQL function in Microsoft SQL Server 2005? 9 answers
在Microsoft SQL Server 2005中模拟group_concat MySQL函数？ 9个答案

I'm trying to get this

我想要得到这个

PatID   FacID   RxNo        RejectCd
121403  463     57074566    R9 
121403  463     57074566    76
121403  463     57074566    88
121403  463     57074566    8C

To look like this

看起来像这样

PatID   FacID   RxNo        RejectCd
121403  463     57074566    R9,76,88,8c

Tried using a pivot or doing it in stages but I'm stuck. Can't pivot because I don't have a field for the column names. Also don't know how many Reject Codes will actually exist till it runs but we can ignore that part for now. I think this will be really challenging for any SQL nerds out there. Thank u!

尝试使用枢轴或分阶段做，但我被卡住了。无法透视，因为我没有列名称的字段。也不知道在运行之前实际存在多少个拒绝代码，但我们现在可以忽略该部分。我认为对于那里的任何SQL书呆子来说这将是非常具有挑战性的。谢谢你！

1 个解决方案

#1

There are a few ways to concatenate row values, but one of the simplest and most common is using for xml path('').

有几种方法可以连接行值，但最简单和最常见的方法之一是使用xml路径（''）。

select
    PatId
  , FacId
  , RxNo
  , RejectCd = stuff(
      (
      select ',' + i.RejectCd
        from t as i
        where i.PatId = t.PatId
          and i.FacId = tFacId
          and i.RxNo  = t.RxNo
      for xml path (''), type).value('.','varchar(max)')
      ,1,1,'')
from t
group by 
    PatId
  , FacId
  , RxNo

in sql server vNext you will be able to use string_agg():

在sql server vNext中，您将能够使用string_agg（）：

select 
    PatId
  , FacId
  , RxNo
  , RejectCd = string_agg (RejectCd, ',') 
      /* optional sort */ within group (order by RejectCd asc) 
from t 
group by 
    PatId
  , FacId
  , RxNo

#1