Incorrect syntax near '.'. for dynamic pivot

Asked Aug 07 '17 at 19:06

Active Aug 07 '17 at 19:11

Viewed 268 times

I am pivoting a table based on https://stackoverflow.com/a/10404455/1349141

This code works perfect. However, when I modify the code for my tables. It shows a mysterious Incorrect syntax near '.' for the row below the second declare.

Here is my code

DECLARE @cols  AS NVARCHAR(MAX)=''
DECLARE @query AS NVARCHAR(MAX)=''
--Incorrect syntax near '.' 
SELECT @cols = @cols + QUOTENAME(Crop) + ',' FROM (select distinct [Crop] from [FAF40].[dbo].[county_farm_acres2014] ) as tmp

set @query = 
'SELECT * from 
(
    select StateCode, CountyCode, Crop from [FAF40].[dbo].[county_farm_acres2014]
) src
pivot 
(
    max(PlantedAcres) for Crop in (' + @cols + ')
) piv'

execute(@query)
go

edited Aug 07 '17 at 19:11

asked Aug 07 '17 at 19:06

Jill Clover

2,168
7
31
51

why # next to from? in ur code... Select StateCode, CountyCode, Crop from # [FAF40].[ – Kannan Kandasamy Aug 07 '17 at 19:08
Edited. It's a typo – Jill Clover Aug 07 '17 at 19:12
Did you debugged by printing your @query? does @cols have more data appended in subquery? – Kannan Kandasamy Aug 07 '17 at 19:13
Thanks. Printed and working on checking the query. – Jill Clover Aug 07 '17 at 19:19
Possibly an extra ',' at end of @cols – KeithL Aug 07 '17 at 19:21
@KeithL You are right. Problem solved. Thanks. – Jill Clover Aug 07 '17 at 19:22
I've always seen people use stuff to remove it – KeithL Aug 07 '17 at 19:22
You also, on line 9, missed the aggregated column PlantedAcres from the select that produces the data (i.e. SELECT PlantedAcres , StateCode, CountyCode, Crop FROM ... ) – Mircea Matei Aug 07 '17 at 21:05

Incorrect syntax near '.'. for dynamic pivot

0 Answers0