Incorrect syntax near 'FOR'

Question

Following code is not working -

SELECT [LY-1], 
       LY = STUFF((SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1,      1, '' ) 
FROM temp1 
GROUP BY [LY-1];

As it is giving below error-

Msg 103010, Level 16, State 1, Line 73 Parse error at line: 2, column: 36: Incorrect syntax near 'temp1'.

Does STUFF and FOR XML are not working Data warehouse ? and is there any alternative for them?

What is `Data Warehouse` mean in this context? Is this just a sql server and your submitting this through a sql client like SSMS or similar? Is this "Azure SQL Data Warehouse" ([In which case this isn't supported](https://learn.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-2017))? The query *looks* right. — JNevill, Apr 29 '19 at 13:11
Is [LY-1] the literal name of a column, or are you trying to run a calculation on the 'LY' column you define in the line below? — Thermos, Apr 29 '19 at 13:20
If you are using Azure SQL Data Warehouse check [this](https://stackoverflow.com/questions/47336785/aggregate-strings-in-azure-sql-data-warehouse) — Andrea, Apr 30 '19 at 08:26

score 0 · Answer 1 · answered Apr 29 '19 at 19:06

I don't think the equal sign is working how you want. Assuming LY is a calculated column and not a column that already exist in the table the query should be:

SELECT [LY-1], 
       STUFF((SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1,      1, '' ) AS LY
FROM temp1 
GROUP BY [LY-1];

For TSQL to create calculated columns you always use the logic first then give the column the name.

Incorrect syntax near 'FOR'

1 Answers1