CODEWITHSUNDEEP
sqlt-sqlsql-server-2016
bautista
bautista

Reputation: 794

Stuff with group by on DATENAME function

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.

Upvotes: 1

Views: 131

Answers (2)

Charlieface
Charlieface

Reputation: 71578

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;

Upvotes: 1

Serg
Serg

Reputation: 22811

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

Upvotes: 1

Related Questions