Convert rows into comma separated values with group by

create table your_table (ID int, ReportId int, Email varchar(500))

insert into your_table select 1, 1, 'a@a.com'insert into your_table select 2, 2, 'b@b.com'insert into your_table select 3, 1, 'c@c.com'insert into your_table select 4, 3, 'd@d.com'insert into your_table select 5, 3, 'e@e.com'

SELECT ReportId, Email =
STUFF((SELECT ', ' + Email
FROM your_table b
WHERE b.ReportId = a.ReportId
FOR XML PATH('')), 1, 2, '')FROM your_table aGROUP BY ReportIdorder by reportID
output -
 ReportId Email
1 a@a.com, c@c.com
2 b@b.com
3 d@d.com, e@e.com

SELECT
Inputreference,
CIR = STUFF((SELECT ', ' + CalculatedInputReference  FROM #tempUC b  WHERE b.InputReference = a.InputReference FOR XML PATH('')), 1, 2, ''),
CID = STUFF((SELECT ', ' + Calculation  FROM #tempUC b  WHERE b.InputReference = a.InputReference FOR XML PATH('')), 1, 2, '')
FROM #tempUC a

GROUP BY InputReference

another simple example:

table structure and table data for namepen
name pen
mike red
mike red
mike blue
mike green
steve red
steve yellow
anton red
anton blue
anton green
anton black
alex black
alex green
alex yellow
alex red

output should be like this:

name (No column name)
alex black,green,yellow,red
anton red,blue,green,black
mike red,red,blue,green
steve red,yellow

query will be
select distinct name ,stuff((select ','+ pen from namepen where namepen.name = n.name for xml path('')),1,1,'')
from namepen n