Categories all dates by month in SQL Server with set-based approach

Question

I have a result set like this:

---------------------------
|        AllDates         |
---------------------------
| 2017-05-12 00:00:00.000 |
| 2017-05-14 00:00:00.000 |
| 2017-08-10 00:00:00.000 | 
| 2017-08-13 00:00:00.000 |
| 2018-02-12 00:00:00.000 | 
| 2018-05-15 00:00:00.000 | 
| 2018-05-16 00:00:00.000 |
---------------------------

Now I need to categorize all the dates into the following string using SQL Server

--------------------------------------------------
|                   Result                       |
--------------------------------------------------
| May: 12, 14, Aug: 10, 13, Feb: 12, May: 15, 16 |
--------------------------------------------------

Without using user defined function in SQL Server, can this be achieve only by using set-based approach?

Answer 1

For SQL Server 2014 you can use this:

DECLARE @DataSource TABLE
(
    [value] DATETIME2
);

INSERT INTO @DataSource ([value])
VALUES ('2017-05-12 00:00:00.000')
      ,('2017-05-14 00:00:00.000')
      ,('2017-08-10 00:00:00.000')
      ,('2017-08-13 00:00:00.000')
      ,('2018-02-12 00:00:00.000')
      ,('2018-05-15 00:00:00.000')
      ,('2018-05-16 00:00:00.000');

WITH DataSource AS
(
    SELECT YEAR([value]) AS [Year]
          ,MONTH([value]) AS [MonthID]
          ,DATENAME(MONTH, [value]) AS [Month]
          ,DAY([value]) AS [Day]
    FROM @DataSource
),
DataSourceDays AS
(
    SELECT DISTINCT [Year]
                   ,[MonthID]
                   ,[MONTH] + ':' + [value] AS [Days]
    FROM DataSource T
    CROSS APPLY
    (
        SELECT STUFF
        (
            (
                SELECT ',' + CAST([Day] AS VARCHAR(2))
                FROM DataSource S
                WHERE T.[Year] = S.[Year]
                    AND T.[MonthID] = S.[MonthID]
                ORDER BY [Day]
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
            ,1
            ,1
            ,''
        )
    ) DS([value])
)
SELECT STUFF
(
    (
        SELECT ', ' + CAST([Days] AS VARCHAR(12))
        FROM DataSourceDays
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,2
    ,''
);

In the first CTE we are getting the details we are going to concatenate. In the second, we are concatenating the days for each month and year. In the last we are concatenating all of the previous results.

---

And FYI, this is how the same is done using SQL Server 2017:

WITH DataSource AS
(
    SELECT YEAR ([value]) AS [Year] 
          ,MONTH([value]) AS [Month]
          ,DATENAME(MONTH, [value]) + ':' + STRING_AGG(DAY([value]), ', ') WITHIN GROUP (ORDER BY [value]) AS [value]
    FROM @DataSource
    GROUP BY YEAR ([value]) 
            ,MONTH([value])
            ,DATENAME(MONTH, [value])
)
SELECT STRING_AGG([value], ' ,') WITHIN GROUP (ORDER BY [Year], [Month])
FROM DataSource;