Pivot Table Error - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Pivot Table Error

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2012-01-20 : 15:18:53

I keep getting the following error

Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '1'.

when I try to execute the below code. According to the error the issue is near the '1' which is within the pivot() after the IN() statement. I don't see what is wrong with the code. Any help is greatly appreciated.


SELECT
 [DIVISION]
,[GROUP_ID]
,[REJECTION_1]
,[ORIGINAL_FSC_NUMBER]
,[ORIGINAL_FSC_NAME]
,[CURRENT_FSC_NUMBER]
,[CURRENT_FSC_NAME]
,[DAY]
,1 AS JAN
,2 AS FEB
,3 AS MAR
,4 AS APR
,5 AS MAY
,6 AS JUN
,7 AS JUL
,8 AS AUG
,9 AS SEP
,10 AS OCT
,11 AS NOV
,12 AS [DEC]

FROM
(
SELECT [DIVISION]
      ,[GROUP_ID]
      ,[Invoice_Count]
      ,[REJECTION_1]
      ,[ORIGINAL_FSC_NUMBER]
      ,[ORIGINAL_FSC_NAME]
      ,[CURRENT_FSC_NUMBER]
      ,[CURRENT_FSC_NAME]
      ,[Date_Stamp]
      ,[MONTH]
      ,[YEAR]
      ,[DAY]
  FROM [ATB_WEEKLY_REJ].dbo.ATB_Rejections_Report
) P

PIVOT 
(SUM([Invoice_Count]) FOR [MONTH] IN('1','2','3','4','5','6','7','8','9','10','11','12')
)AS PVT

GROUP BY
 [DIVISION]
,[GROUP_ID]
,[REJECTION_1]
,[ORIGINAL_FSC_NUMBER]
,[ORIGINAL_FSC_NAME]
,[CURRENT_FSC_NUMBER]
,[CURRENT_FSC_NAME]
,[DAY]
,1
,2
,3
,4
,5
,6
,7
,8
,9
,10
,11
,12

Brian

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 15:26:58

Use square brackets


...
(SUM([Invoice_Count]) FOR [MONTH] IN([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources