CODEWITHSUNDEEP
sqlsql-serveraggregate-functionsstring-function
Abhijeet Raj
Abhijeet Raj

Reputation: 81

Can someone find the syntax error in this simple query?

Please help me with this error.

SELECT StateProvince,STRING_AGG(AddressID, ',') WITHIN GROUP (ORDER BY AddressID)
FROM [SalesLT].[Address] GROUP BY StateProvince;

I can't find the error in this but it says

Incorrect syntax near '('.

Upvotes: 2

Views: 2403

Answers (3)

Helmut Schwarzin
Helmut Schwarzin

Reputation: 71

Had the same issue after upgrading a database from SQL 2008R2 to 2017. Fixed by setting the Compatability Level to 110 (or higher?)

Upvotes: 7

Cetin Basoz
Cetin Basoz

Reputation: 23797

I can't see an error correlated to the message you are getting. With large data it would complain about 8000 bytes limit. This works fine under MS SQL 2017, AdventureWorks sample database (not the lite one with fewer data):

SELECT StateProvinceID,
       STRING_AGG(cast(AddressID as varchar(MAX)), ',') WITHIN GROUP (ORDER BY AddressID) as AdressIDS
FROM [Person].[Address] 
GROUP BY StateProvinceID;

Upvotes: 0

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

FOR SQL SERVER 2017

SELECT StateProvince,
       STRING_AGG(AddressID, ',') WITHIN GROUP (ORDER BY AddressID) AS AddressID
FROM [SalesLT].[Address] GROUP BY StateProvince;

DEMO

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=69e5f9e9c5f3cab62e4b2eb9fb678299

FOR SQL SERVER Below 2017

SELECT
      StateProvince, 
      AddressID = STUFF((
          SELECT ',' + CAST(md.AddressID AS NVARCHAR)
          FROM [SalesLT].[Address] md
          WHERE m.StateProvince = md.StateProvince
          ORDER BY AddressID
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM [SalesLT].[Address] m
Group by StateProvince

DEMO

http://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=a1722450c70c946e9b53ae23785f4919

Upvotes: 2

Related Questions