Using distinct with stuff/for xml path('')
Use select distinct
in the subquery:
select rc.Routage,
count(distinct rc.Event),
stuff((select distinct ', ' + cast(rcA.Event as varchar(max))
from Receiving rcA
where rcA.supplier = 'user' and
rcA.DATETIME > '20170322' and
rc.Routage = rcA.Routage
for xml path('')
), 1, 2, '')
from Receiving rc
where rc.supplier = 'user' and rc.DATETIME > '20170322'
group by rc.Routage;
Notes:
- In SQL Server, never use
varchar()
(or related types) without a length. The default varies by context and you are (potentially) introducing a bug that is really hard to find. - You want the
stuff()
to remove two characters, not 1, because you have a comma followed by a space. - This formulation assumes that
Event
does not have XML special characters. It is easy to tweak if that is an issue.
Also, this type of query is usually faster if you eliminate the duplicates in a subquery:
select rc.Routage, rc.numEvents,
stuff((select distinct ', ' + cast(rcA.Event as varchar(max))
from Receiving rcA
where rcA.supplier = 'user' and
rcA.DATETIME > '20170322' and
rc.Routage = rcA.Routage
for xml path(''), type
).value('.', 'varchar(max)'
), 1, 2, ''
)
from (select rc.Routage, count(distinct rc.Event) as numEvents
from Receiving rc
where rc.supplier = 'user' and rc.DATETIME > '20170322'
group by rc.Routage
) rc;
Do the distinct
in a subquery, before the XML processing gets anywhere near it:
select rc.Routage
, COUNT(distinct rc.Event)
, STUFF((select ', ' + cast(rcA.Event as varchar)
from (select distinct Event from Receiving a
where supplier = 'user'
and DATETIME > '20170322'
and rc.Routage=a.Routage
) rcA
for xml path(''))
, 1, 1, '')
from Receiving rc
where rc.supplier = 'user'
and rc.DATETIME > '20170322'
group by rc.Routage
order by COUNT(distinct rc.Event)desc