July 22, 2010 at 11:16 pm
hi,
I m using Pivot in my query to generate the columns as
[Code]
Select * from Table
PIVOT
(
SUM(Amnt)
For date in ([2010-06-08 00:00:00.000],[2010-06-21 00:00:00.000])
)AS PVT
[/Code]
My problem is that i want to use Week instead of Date in above query
i used datepart function as
For datepart(wk,date) in ([24],[25])
But is giving error
Incorrect syntax near '('.
cant we use any funciton in PIVOT clause ,,or is there any better option
July 22, 2010 at 11:25 pm
honey47 (7/22/2010)
hi,But is giving error
Incorrect syntax near '('.
cant we use any funciton in PIVOT clause ,,or is there any better option
No we can not.. FOR clause if used to specify a "column NAME"..
If you really want to use WEEK, the i suggest u use a CTE to first extract the DATEPART and the use that column in the FOR Clause..
Like :
SELECT * FROM
(
Select * , datepart(wk,date) dtprt from Table
) pivot_Table
PIVOT
(
SUM(Amnt) For dtprt in ([24],[25])
) AS PVT
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply