Situation:

Let there be a table ALPHA which contains a column ALPHA.ID_BETA (Foreign Key on Table BETA) and a column ALPHA.CREATIONDATE (DATETIME NOT NULL).

Now assume the following records in Table ALPHA:

Now imagine Table BETA to look like this (i left out other columns for simplicity:

Desired Output: Is a value that concatenates all values (Format: DATENAME YYYY) of CREATIONDATE for a single ID_BETA Ordered by date ascending. In this example, the output should be January 2022, February 2022, May 2022 (obviously depending on Language settings)

What I have tried:

SELECT STUFF(
               (SELECT ', ' 
  DATENAME(MONTH,(ALPHA.CREATIONDATE))   DATENAME(YEAR, ALPHA.CREATIONDATE)
                FROM ALPHA
                WHERE ALPHA.ID_BETA = 1
                GROUP BY ALPHA.CREATIONDATE
                ORDER BY ALPHA.CREATIONDATE ASC
                FOR XML PATH('')),1, 1, '')

This however will not give me distinct values. Trying out the obvious DISTINCT statement gives me the following error:

Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Note that I cannot solve this problem with the "new" STRING_AGG function since it's only supported from SQL-Server2017 upwards.

CodePudding user response：

As an error mesage says, order by exactly the expression in the select clause

       SELECT STUFF(
           (SELECT distinct ', ' 
    DATENAME(MONTH,(ALPHA.CREATIONDATE))   DATENAME(YEAR, ALPHA.CREATIONDATE)
            FROM ALPHA
            WHERE ALPHA.ID_BETA = 1
            GROUP BY ALPHA.CREATIONDATE
            ORDER BY ', ' 
    DATENAME(MONTH,(ALPHA.CREATIONDATE))   DATENAME(YEAR, ALPHA.CREATIONDATE) ASC
            FOR XML PATH('')),1, 1, '')

CodePudding user response：

You need to group and sort by those two calculated columns, rather than by CREATIONDATE.

Note also that you need .value to unescape the XML, and the third parameter of STUFF should be the same as the length of the separator

SELECT STUFF(
               (SELECT
                  ', '   DATENAME(MONTH, (a.CREATIONDATE))   DATENAME(YEAR, a.CREATIONDATE)
                FROM ALPHA a
                WHERE a.ID_BETA = 1
                GROUP BY
                  DATENAME(YEAR, a.CREATIONDATE),
                  DATENAME(MONTH, a.CREATIONDATE)
                ORDER BY
                  DATENAME(YEAR, a.CREATIONDATE),
                  DATENAME(MONTH, a.CREATIONDATE)
                FOR XML PATH(''), TYPE
           ).value('text()[1]','nvarchar(max)'), 1, LEN(', '), '')

If you want to do this per row of BETA you can use CROSS APPLY or a subquery:

SELECT STUFF(
               (SELECT
                  ', '   DATENAME(MONTH, (a.CREATIONDATE))   DATENAME(YEAR, a.CREATIONDATE)
                FROM ALPHA a
                WHERE a.ID_BETA = b.ID
                GROUP BY
                  DATENAME(YEAR, a.CREATIONDATE),
                  DATENAME(MONTH, a.CREATIONDATE)
                ORDER BY
                  DATENAME(YEAR, a.CREATIONDATE),
                  DATENAME(MONTH, a.CREATIONDATE)
                FOR XML PATH(''), TYPE
           ).value('text()[1]','nvarchar(max)'), 1, LEN(', '), '')

FROM BETA b;

You can also use EOMONTH instead of DATENAME to get a single date per month.