PIVOT and IF for the FROM clause.

Question

PIVOT and IF for the FROM clause.

greasypole

SSC-Addicted

Points: 439
More actions
October 15, 2009 at 9:20 am

#215577

Not 100% if it can be done, and if not why not... I need an explaination to help me understand what I am doing wrong.
Basically I want to use an IF statement to chnage the FROM clause that returns the underlying data for my PIVOT. The reason I want to do this is to change the level of the report the PIVOT will return.
SELECT * FROM
(
IF @Type = 1
BEGIN
SELECT SalesOption, SODesc, Orders, MAXAction
FROM vw_MAXAction
GROUP BY YearWeek, BrkPrmDte, SalesOption, SODesc, Orders, MAXAction
END
ELSE
BEGIN
SELECT YearWeek, BrkPrmDte, SalesOption, SODesc, Orders, MAXAction
FROM vw_MaxAction
GROUP BY YearWeek, BrkPrmDte, SalesOption, SODesc, Orders, MAXAction
HAVING (YearWeek = @Week)
END
)
AS t
PIVOT
(
SUM(Orders) FOR MAXAction IN ([Wait],[Cancel],[Sys Alloc],[Re-Instate],[Unable To Contact],[Answer Machine],[DToCMail],[MailMerged],[Admin Update],[Newer Promise Date],[No Action Req.],[Part Del.],[Unactioned])
) AS p
Hope that makes sense.

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

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

You can't use "IF" as part of a query. It's only for flow control.

You'll need to split your pivot up into two separate SELECT's. Then you can us "IF" to determine which SELECT you want to run.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)