views:

98

answers:

1

When I do this:

select col1,case when [pivot1]=1 then '-' else '' end [pivot1],
case when [pivot2]=1 then '-' else '' end [pivot2]
from
(select col1,col2,col3 from tbl) as c
pivot
(sum(col3) for col2 in
([pivot1],[pivot2]))as pvt

Everything works fine.

When I do this:

select col1,[pivot1],[pivot2]
from
(select col1,col2,col3 from tbl) as c
pivot
(sum(case col3 when '-' then 1 else 0 end) for col2 in
([pivot1],[pivot2]))as pvt

I get the following error:

"Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'case'."

My intention is to write a single case statement rather than multiple ones for this conversion.

What am I doing wrong?

+5  A: 

Replace your case statement with

case when col3 = '-' then 1 else 0 end
Greco
Still I am getting the same error message
priyanka.sarkar
I would approach it a bit differently:DECLARE @T TABLE (Amount MONEY, RegionId NVARCHAR(5));INSERT INTO @TSELECT SUM(CASE WHEN col3 != 0 THEN 1 ELSE 0 END), col2FROM tblGROUP BY col2SELECT * FROM @TPIVOT( SUM (Amount) FOR [RegionId] IN ([US], [CA])) AS p
Greco
But I cannot insert value.. It is already there in the table. I have to perform the logic after picking up the values from Database
priyanka.sarkar
Well, seems to me that you did not fully understand the code I provided you. The aim is to take the expression out of the pivot.Therefore you filling a temp table variable with the values you need for your pivot and then build the pivot against the already correctly computed values.
Greco
Ok Ok.. I got now. so that means whatever Mr.Madhivanan has said is correct .i.e. i n the cvase of Pivot we cannot use any case statement inside an aggregate function. I got now. That means I can never achieve that. But 1 thing, I can use case statement inside a aggregate function if it is not inside a Pivot. Thwen what is the problem with Pivot?
priyanka.sarkar