October 25, 2007 at 4:50 pm
I have a table that has the following columns: EffYrMo, Status, ChkAmt, ClaimNo. Statuses are Open, Closed and Pending. I want to pivot on both ChkAmt and ClaimNo, so my results will be one table like below:
EffYrMo CloseAmt OpenAmt PendAmt CloseClmCnt OpenClmCnt PendClmCnt
200710 100.00 5.00 25.00 20 1 5
200709 90.00 10.00 20.00 18 2 4
The problem I'm running into is that SQL Server keeps complaining when I try to do a double pivot or two columns in the clause after the PIVOT operator. Here are a couple of examples of what I attempted (and failed) to do:
Select EffYrMo, [C] as CAmt, [O] as OAmt, [P] as PAm, [C] as CCnt, [O] as OCnt, [P] as PCnt
from (Select EffYrMo, ClaimStatus, Round(isnull(CheckAmount,0.00),0,0) as ChkAmt, (isnull(ClaimNumber,0)) as ClaimCnt
From Claims) as ChkAmtPer
PIVOT
(Sum(ChkAmt), Count(ClaimCnt) for ClaimStatus in ([C], [O], [P] ) ) as ClmCnt
Order by EffYrMo;
--Attempt 1
Select EffYrMo, [C] as CAmt, [O] as OAmt, [P] as PAm, [C] as CCnt, [O] as OCnt, [P] as PCnt
from (Select EffYrMo, ClaimStatus, Round(isnull(CheckAmount,0.00),0,0) as ChkAmt, (isnull(ClaimNumber,0)) as ClaimCnt
From Claims) as ChkAmtPer
PIVOT
(Sum(ChkAmt) for ClaimStatus IN ([C], [O], [P] ) ) as LagPaid
PIVOT
(Count(ClaimCnt) for ClaimStatus in ([C], [O], [P] ) ) as ClmCnt
Order by EffYrMo;
--Attempt 2
Has anyone managed to pivot 2 sets of columns in the same query before? If so, can you give me pointers? I would like to do this WITHOUT joining two Pivot Tables to get my result set.
Thanks in advance!!!
October 25, 2007 at 10:25 pm
Each PIVOT operation can only affect one aggreation operation at a time. If you need two separate aggregate operations - use them as separate derived tables and join them by the row grouping field(s). Not what you wanted - but from what I can tell- that's the only "legit" way to use PIVOT.
something like
SELECT
o.[1],o.[2] ,o.[3] ,o.[4],o.[5],o.[6],o.[7],o.[8],o.[9],o.[10],
o.[11],o.[12] ,o.[13] ,o.[14],o.[15],o.[16],o.[17],o.[18],o.[19],o.[20],
o2.[1],o2.[2] ,o2.[3] ,o2.[4],o2.[5],o2.[6],o2.[7],o2.[8],o2.[9],o2.[10],
o2.[11],o2.[12] ,o2.[13] ,o2.[14],o2.[15],o2.[16],o2.[17],o2.[18],o2.[19],o2.[20]
FROM (select coid,prodid,orderamount from testpivot) t
PIVOT (sum(orderamount) FOR coid IN ([1], [2],
[3],[4],[5],[6],[7],[8],[9],[10],[11], [12],
[13],[14],[15],[16],[17],[18],[19],[20])
) AS O
inner join
(select coid,prodid,orderamount from testpivot) t
PIVOT (avg(orderamount) FOR coid IN ([1] , [2],
[3],[4],[5],[6],[7],[8],[9],[10],[11], [12],
[13],[14],[15],[16],[17],[18],[19],[20])
) AS O2 on O.prodid=O2.prodid
This is one where the CASE statement works a lot better (one single pass through the data, not 2), even though it does take quite a bit longer to write 🙂
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 26, 2007 at 4:12 am
Matt,
I absolutely LOVE your .sig! @=)
As far as your answer goes... Hmm. I was afraid you were going to say that. Oh, well. In this particular situation, the JOIN doesn't take too terribly long and the columns are always going to be Closed, Open or Pending, so I guess I'll stick with the JOIN.
Thanks for verifying, though. I appreciate it.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply