CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
priyanka.sarkar
priyanka.sarkar

Reputation: 26508

What is wrong with the following statement?

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?

Upvotes: 0

Views: 273

Answers (1)

Dimi Takis
Dimi Takis

Reputation: 4959

Replace your case statement with

case when col3 = '-' then 1 else 0 end

Upvotes: 5

Related Questions