Stuff with group by on DATENAME function

user_11051950 0
Viewed 60

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:

ID CREATIONDATE (YYYY-MM-DD) ID_BETA
1 2022-05-26 00:00:00.000 1
2 2022-02-02 00:00:00.000 1
3 2022-01-28 00:00:00.000 1
4 2022-01-02 00:00:00.000 1

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

ID
1

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.

sql-server-2016 tsql sql
2 Answers

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

You need to group and sort by EOMONTH(CREATIONDATE) in order to group by a single date per month, rather than grouping just 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, EOMONTH(a.CREATIONDATE)) + DATENAME(YEAR, EOMONTH(a.CREATIONDATE))
                FROM ALPHA a
                WHERE a.ID_BETA = 1
                GROUP BY
                  EOMONTH(a.CREATIONDATE)
                ORDER BY
                  EOMONTH(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, EOMONTH(a.CREATIONDATE)) + DATENAME(YEAR, EOMONTH(a.CREATIONDATE))
                FROM ALPHA a
                WHERE a.ID_BETA = b.ID
                GROUP BY
                  EOMONTH(a.CREATIONDATE)
                ORDER BY
                  EOMONTH(a.CREATIONDATE)
                FOR XML PATH(''), TYPE
           ).value('text()[1]','nvarchar(max)'), 1, LEN(', '), '')

FROM BETA b;
user_148689970
Related