Using PIVOT Operator - 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 2005 Forums

Transact-SQL (2005)

Using PIVOT Operator

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-14 : 01:24:55

I wrote the query using pivot operator but its error
'Incorrect Syntax near ( '

Can any one find this, My code as below

CREATE TABLE #Sales
(
OrderYear DATETIME NOT NULL,
SalesPersonID int NOT NULL,
Total money NOT NULL
)

GO

INSERT INTO #Sales SELECT '4/23/2005' ,230, 4500
INSERT INTO #Sales SELECT '12/2/2005' ,240, 5000
INSERT INTO #Sales SELECT '11/15/2006' ,230, 6000
INSERT INTO #Sales SELECT '9/6/2006' ,240, 2000
INSERT INTO #Sales SELECT '7/25/2005' ,250, 8000
INSERT INTO #Sales SELECT '6/20/2007' ,230, 6500
INSERT INTO #Sales SELECT '1/31/2007' ,250, 5000
Go
SELECT * FROM #Sales

SELECT SalesPersonID,
SUM(CASE when YEAR(OrderDate) = 2005 then Total ELSE 0 end) as '2005',
SUM(CASE when YEAR(OrderDate) = 2006 then Total ELSE 0 end) as '2006',
SUM(CASE when YEAR(OrderDate) = 2007 then Total ELSE 0 end) as '2007'
FROM #Sales
GROUP BY SalesPersonID

SELECT SalesPersonID, [2005], [2006], [2007] FROM #Sales
PIVOT (SUM(Total) FOR YEAR(OrderDate) IN ([2005],[2006],[2007]))
AS SalesPivot ORDER BY SalesPersonID

Is it possible to use 'YEAR(OrderDate)' In (SUM(Total) FOR YEAR(OrderDate) IN ([2005],[2006],[2007]))

Kindly reply

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-14 : 03:20:02

SELECT SalesPersonID, [2005], [2006], [2007] FROM
(select SalesPersonID, Total,year(orderyear) as orderyear from #Sales) p
PIVOT (SUM(Total) FOR Orderyear IN ([2005],[2006],[2007]))
AS SalesPivot ORDER BY SalesPersonID

Madhivanan

Failing to plan is Planning to fail