How to summarize a query separating with commas

I have a list of dates with folios, one folio can have multiple dates. The folio is the employee holiday request folio. (SQL Server 2008)

Instead of showing that info like a list I need to show it in the following format:

Here is the SQL Fiddle:

http://sqlfiddle.com/#!3/0ddd2/7

Answer

Larry’s answer is basically correct (and I’m upvoting it). But it has some shortcomings:

• It leaves a comma at the end.
• It uses varchar() without a length.
• And, it misdescribes the role of stuff().

The string concatenation is actually done by for xml path, not by stuff(). stuff() just removes the leading comma.

A better formulation dispenses with the unnecessarily levels of subqueries. In addition, I strongly advocate using date part names in datepart(). Who wants to remember if mm means minutes or months, when you can use “minute” and “month” respectively?

SELECT hi.holidayid as Folio,
       STUFF((SELECT ',' + CAST(DATEPART(day, hi2.date) AS VARCHAR(255))
              FROM employee_holiday_item hi2
              WHERE hi2.holidayid = hi.holidayid AND
                    DATEPART(month, hi.date) = DATEPART(month, hi2.date) AND
                    DATEPART(year, hi.date) = DATEPART(year,hi2.date)
              ORDER BY DATEPART(dd,hi2.date)
              FOR XML PATH ('')
             ), 1, 1, '') as dates,
       DATEPART(month, hi.date) AS Month,
       DATEPART(year, hi.date) AS Year,
       COUNT(*) AS 'Total Days'
FROM employee_holiday_item hi
GROUP BY hi.holidayid, DATEPART(month,hi.date), DATEPART(year,hi.date)

Here is a SQL Fiddle.