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
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
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