How to summarize a query separating with commas

Question

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:

enter image description here

Here is the SQL Fiddle:

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

Also: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — jpw, May 17 '15 at 22:59
This is not a SQL problem, you should be doing display presentation in your UI layer. SQL is a data persistence language, not a data formatting language. i.e. "You are using a hammer to tighten a bolt" — TFD, May 18 '15 at 01:50

score 6 · Answer 1 · edited May 17 '15 at 23:26

This can be accomplished using the STUFF function with subqueries. Syntax would be:

SELECT Result2.Folio,
       LEFT(Result2.Dates, LEN(Result2.Dates) -1) AS Dates,
       Result2.Month,
       Result2.Year,
       Result2.[Total Days]
FROM
(
    SELECT Result.Folio,
           STUFF((SELECT ' ' + CAST(DATEPART(dd,hi2.date) AS VARCHAR) + ','
    FROM employee_holiday_item hi2
    WHERE hi2.holidayid = result.Folio
          AND Result.Month = DATEPART(mm,hi2.date)
          AND Result.Year = DATEPART(yyyy,hi2.date)
    ORDER BY DATEPART(dd,hi2.date)
          FOR XML PATH ('') ),1,1,'') AS 'Dates',
          Result.Month,
          Result.Year,
          Result.[Total Days]
    FROM (
          SELECT hi.holidayid as Folio,
                 DATEPART(mm,hi.date) AS Month,
                 DATEPART(yyyy,hi.date) AS Year,
                 COUNT(*) AS 'Total Days'
          FROM employee_holiday_item hi
          GROUP BY hi.holidayid, DATEPART(mm,hi.date), DATEPART(yyyy,hi.date)
        ) AS Result
) AS Result2

score 5 · Accepted Answer · answered May 18 '15 at 00:04

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.

How to summarize a query separating with commas

2 Answers2