sql server - SQL: How to CONCAT value -
how can return values of mainemail in query below, delimited commas , still count mdcselect? declare @mainhospital varchar(50)='hospital 1' select distinct mainhospital , f.item, count(*) count surveypiclist s cross apply splitstrings(s.mdcselect,':') f mainhospital = @mainhospital group mainhospital, f.item order count desc to clear above returns this: http://i.imgur.com/f1opu6p.jpg so there 3 separate entries/people selected "02-eye". want list out emails(mainemail) comma delimited. please let me know if unclear. assuming use of cross apply using sql server, , @ least version 2005, can use xml concatenation follows: declare @mainhospital varchar(50)='hospital 1'; select mainhospital , f.item, count(*) count ,stuff( (select distinct ', ' + m.mainemail surveypiclist m m.mainhospital = @mainhospital , ':' + m.mdcselect + ':'...