Multiple use of pivot on the same header column

Question

Multiple use of pivot on the same header column

Konrad Kubiak

Grasshopper

Points: 16
More actions
June 3, 2008 at 3:41 am

#380901

Hello,
is it possible to use mulitple pivot (to aggregate many columns) over the same header column?
I mean sth like:
PIVOT ( Sum(MaxCapacity) FOR Actual1 IN ( [0] ,[1] ) ) as ep1
PIVOT ( Sum(MinCapacity) FOR Actual2 IN ( [2] ,[3] ) ) as ep2
where Actual1 and Actual2 are aliases of the column PLD_ActualsCollected definde like:
PLD_ActualsCollected as Actual1,
PLD_ActualsCollected + 2 as Actual2,
Of course I can provide the whole query.
From what I see on the forum it is not possible and I will have to revert to use inner join over many subqueries.
The problem is that this has to work for more than 2 aggregated columns and subqueries for the pivot already contain a lot of unions, unpivots done over many databases located on different hosts.
Thank you in advance
Konrad

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Konrad Kubiak (6/3/2008)
Hello,
is it possible to use mulitple pivot (to aggregate many columns) over the same header column?
I mean sth like:
PIVOT ( Sum(MaxCapacity) FOR Actual1 IN ( [0] ,[1] ) ) as ep1
PIVOT ( Sum(MinCapacity) FOR Actual2 IN ( [2] ,[3] ) ) as ep2
where Actual1 and Actual2 are aliases of the column PLD_ActualsCollected definde like:
PLD_ActualsCollected as Actual1,
PLD_ActualsCollected + 2 as Actual2,
Of course I can provide the whole query.
From what I see on the forum it is not possible and I will have to revert to use inner join over many subqueries.
The problem is that this has to work for more than 2 aggregated columns and subqueries for the pivot already contain a lot of unions, unpivots done over many databases located on different hosts.
Thank you in advance
Konrad

One of the limitations of the PIVOT operator is that it supports just "one" agrregation function ...

There are ways around it that involve inline queries. I still for these cases use the CASE expressions to pivot through "multiple" agrregated columns.

Just my $0.02

* Noel

Konrad Kubiak Grasshopper Points: 16 More actions · Answer 2

Thanks Noel,

luckily we were able to solve our problem another way so that we end up with pivoting just one column. Anyway it was good exercise :w00t:

Best Regards

Konrad