有人可以在这个简单的查询中找到语法错​​误吗？

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 '('.

'('附近的语法不正确。

2 个解决方案

#1

FOR SQL SERVER 2017

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

适用于2017年以下的SQL Server

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

#2

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):

我看不到与您收到的消息相关的错误。对于大数据,它会抱怨大约8000字节的限制。这在MS SQL 2017,AdventureWorks示例数据库(不是具有较少数据的精简数据库)下工作正常:

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