类别所有日期由本月在SQL Server和基于集合的方法

Question

我有一个结果集是这样的：

--------------------------- 
|  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 | 
--------------------------- 

现在我需要的所有日期归类为以下字符串使用SQL Server

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

不使用SQL Server中的用户定义函数，只能使用基于集合的方法才能实现这个功能吗？

Answer 1

对于SQL Server 2014，你可以使用这个：

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 
    ,'' 
); 

在第一CTE我们得到我们要拼接的细节。第二，我们连接每个月和每年的日子。最后，我们将所有前面的结果连接起来。

---

，仅供参考，这是怎样的同样是使用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;