Thursday, February 18, 2016

Note to self - concatenating rows into string with MSSQL

Here is a sample code:

select count(a.v2_icid), a.v2_icid,
(select distinct(fullname) + ',' AS [text()] from systemuser u 
join account acc on acc.ownerid = u.systemuserid 
where acc.v2_icid = a.v2_icid For XML PATH ('')) [Users]
from account a
where statecode = 0 and a.v2_icid is not null
group by a.v2_icid
having count(a.v2_icid) > 1
order by a.v2_icid

No comments: