Using CASE statement within a PIVOT

Question

Post reply

Using CASE statement within a PIVOT

SteveEClarke

SSCertifiable

Points: 5161
More actions
June 17, 2014 at 8:58 am

#290184

Hi Guys,
I am using a PIVOT function to obtain the Invoice Values, but they appear in different currencies so need to perform a case function.
But am struggling with the syntax;
This fails a syntax check with
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'Case'.
The script I have is as follows;
SELECT
TrnYear,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM
( SELECT
TrnYear, TrnMonth, PostValue
FROM [ApInvoicePay]
Where TrnYear >= Year( GetDate() ) -1 ) p
PIVOT
(
Sum( Case when PostCurrency = 'GBP' then PostValue else PostValue / PostConvRate end )
FOR TrnMonth IN
( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS Pvt
GO
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Apply your currency rate conversion before PIVOT:

....

(SELECT TrnYear, TrnMonth, CASE WHEN PostCurrency = 'GBP' THEN PostValue ELSE PostValue / PostConvRate END AS PostValue

FROM [ApInvoicePay]

Where TrnYear >= Year( GetDate() ) -1 ) P

...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

Thank you.

Looking past the selection into the PIVOT ! - sometimes look too hard for a solution to a problem that is never there.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Using CASE statement within a PIVOT

Cookies on SQLServerCentral