PIVOT Error as Incorrect syntax near '('

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT Error as Incorrect syntax near '('

Author

Topic

tonsetony
Starting Member

4 Posts

Posted - 2012-07-26 : 18:05:00

The below query gives the error as Incorrect syntax near '('. :
Can you please help me where I am wrong?



<pre>
SELECT DDate,COALESCE([February],0) AS [February], COALESCE([August],0) AS [August], COALESCE([April],0) AS [April], COALESCE([December],0) AS [December], COALESCE([September],0) AS [September], COALESCE([January],0) AS [January], COALESCE([October],0) AS [October] FROM
( select DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate,Amount1 from data where code=1257 
) as SourceTable
PIVOT 
( 
sum(Amount1) 
FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate IN([February],[August],[April],[December],[September],[January],[October])
)
</pre>

Tony Dsouza
---------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 18:17:32

[code]SELECT p.DDate,
COALESCE(p.February, 0) AS [February],
COALESCE(p.August, 0) AS [August],
COALESCE(p.April, 0) AS [April],
COALESCE(p.December, 0) AS [December],
COALESCE(p.September, 0) AS [September],
COALESCE(p.January, 0) AS [January],
COALESCE(p.October, 0) AS [October]
FROM (
SELECT DATENAME(MONTH, dbo.ConvertIntToDate(Date_)) AS DDate,
Amount1
FROM dbo.Data
WHERE Code = 1257
) AS s
PIVOT (
SUM(s.Amount1)
FOR s.DDate IN([February], [August], [April], [December], [September], [January], [October])
) AS p[/code]

N 56°04'39.26"
E 12°55'05.63"

tonsetony
Starting Member

4 Posts

Posted - 2012-07-27 : 03:38:55

Hey, thanks for the reply. The query i wrote is from Message results i.e from print command.
Here is the original query :

DECLARE @MONTHS NVARCHAR(MAX),@MONTHSIN NVARCHAR(MAX)
SELECT @MONTHS = STUFF((select ',' + quotename(DDate) 
FROM (select distinct DATENAME(mm,dbo.ConvertIntToDate(Date_)) as DDate from data) X FOR XML PATH('')) ,1,1,'')

SELECT @MONTHSIN= STUFF((select ', COALESCE(' + quotename(DDate)+ ',0) AS ' + quotename(DDate) 
FROM   (select distinct DATENAME(mm,dbo.ConvertIntToDate(Date_)) as DDate from data) X FOR XML PATH('') ),1,2,'')

SELECT @MONTHS = SUBSTRING(@MONTHS,1,LEN(@MONTHS)-1)
 print @MONTHS;
DECLARE @SQLSTR NVARCHAR(MAX)
SELECT @SQLSTR = 
'SELECT DDate,'+ @MONTHSIN + ' FROM
( select DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate,Amount1 from data where code=1257 
) as SourceTable
PIVOT 
( 
sum(Amount1) 
FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate IN(' + @MONTHS + ')
)'
print @SQLSTR
execute @SQLSTR
--'PRINT @MONTHS;

I tried your query.. it gives me the below error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DDate'.

Tony Dsouza
---------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-27 : 08:09:23

Replace

FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate IN(' + @MONTHS + ')

with

FOR DDate IN(' + @MONTHS + ')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources