locked
STRING_AGG WITHIN GROUP syntax error RRS feed

  • Question

  • When I do this select

     SELECT Request_ID, STRING_AGG(Proc_Code_Base, ',' ) as PCB
      FROM #TEMP_CASE_PROC_LIST
      GROUP BY Request_ID

    It works fine

    but this

     SELECT Request_ID, STRING_AGG(Proc_Code_Base, ',' ) WITHIN GROUP (ORDER BY Proc_Code_Base)
     as PCB
      FROM #TEMP_CASE_PROC_LIST
      GROUP BY Request_ID

    Adding the WITHIN GROUP gives me this error

    Msg 102, Level 15, State 1, Line 14
    Incorrect syntax near '('.

    I'm running SQL Server 2017


    Friday, May 8, 2020 10:13 PM

All replies

  • Please try this

    SELECT Request_ID, STRING_AGG(Proc_Code_Base, ',' ) WITHIN GROUP (ORDER BY Proc_Code_Base ASC)
     as PCB
      FROM #TEMP_CASE_PROC_LIST
      GROUP BY Request_ID


    http://uk.linkedin.com/in/ramjaddu

    Friday, May 8, 2020 10:26 PM
  • Open a new query window in SSMS and run your both queries again.

    Both queries are ok.

    Friday, May 8, 2020 10:53 PM
  • My guess is your database compatibility is set to 100.  It has to be at least 110 to use the WITHIN GROUP option of STRING_AGG.

    To see your compatibility level, run

    SELECT name, compatibility_level
    FROM sys.databases
    WHERE name = db_name();

    You can change the compatibility level with the ALTER DATABASE command.  But doing so might change the way some of your queries operate.  You may want to review https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15 which discusses some (but not all) of the differences.

    Tom

    • Proposed as answer by Lily Lii Tuesday, May 12, 2020 7:38 AM
    Friday, May 8, 2020 11:15 PM
  • Hi Eric S. Fanwick,

    Please make sure all users are connecting to the same SQL Server and running queries in the same database, and all databases are at the latest compatibility level.

    There are some similar threads:

    STRING_AGG used with WITHIN GROUP throws a syntax error in some users SSMS.

    Issue with STRING_AGG WITHIN GROUP.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 11, 2020 7:12 AM
  • Hi Eric S. Fanwick,

    Do the answers above help you? Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 12, 2020 7:39 AM