Multiple Pivot with an existing sql query working contains pivot-CodePudding

I have an SQL query working with Pivot, I have some plans records (table Plan) and on each plan I have an amount each month (table PlanMonth). this is an example how it works with pivot.

select PlanId,[1]    Jan,[2]Feb,[3]Mar,[4]Apr,[5]May,[6]Jun,[7] July,[8]Aug,[9]Sep,[10]Oct,[11]Nov,[12] Dec
from(
select Plan.Id PlanId,PlanMonth.Amount,PlanMonth.Month 
from PlanTable

left join PlanMonth on
LockAmount.IdPlan = rpo.Id
)p
PIVOT
(
SUM (Amount)
FOR Month IN
( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt

and I want to add another more columns from another table (LockAmount table) linked by IdPlan and this is the example:

select PlanId,[1] LockAmount1,[2] LockAmount2,[3] LockAmount3,[4] LockAmount4 
from(
select Plan.Id PlanId,LockAmountTable.LockAmount,LockAmountTable.NumForecast 
from PlanTable

left join LockAmountTable on
LockAmount.IdPlan = rpo.Id
)p
PIVOT
(
SUM (LockAmount)
FOR NumForecast IN
( [1][1],[2],[3],[4])
) AS pvt

this is how the table structure is done Tables

My question how to apply multiple pivot to show the data in this form form

CodePudding user response：

From what I understand from the question is can we have multiple pivots and want to integrate using both the queries shared. Yes, we can go with the approach of CTE until same column is present in both the sub-queries. Below is the query that might be helpful.

; with pivot_1 as
(
select pvt1.PlanId as pvt1PlanID,pvt1.[1] Jan,pvt1.[2]Feb,pvt1.[3]Mar,pvt1.[4]Apr,pvt1.[5]May,pvt1.[6]Jun,pvt1.[7] July,pvt1.[8]Aug,pvt1.[9]Sep,pvt1.[10]Oct,pvt1.[11]Nov,pvt1.[12] Dec
from(
select Plan.Id PlanId,PlanMonth.Amount,PlanMonth.Month 
from PlanTable
left join PlanMonth on
LockAmount.IdPlan = rpo.Id
)p
PIVOT
(
SUM (Amount)
FOR Month IN
( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt1
), pivot_2 as (
select pvt2.PlanId as pvt2PlanID,pvt2.[1] LockAmount1,pvt2.[2] LockAmount2,pvt2.[3] LockAmount3,pvt2.[4] LockAmount4 
from(
select Plan.Id PlanId,LockAmountTable.LockAmount,LockAmountTable.NumForecast 
from PlanTable

left join LockAmountTable on
LockAmount.IdPlan = rpo.Id
)p
PIVOT
(
SUM (LockAmount)
FOR NumForecast IN
( [1][1],[2],[3],[4])
) AS pvt2)
select * from pivot_1 p1 
inner join pivot_2 p2 on p1.pvt1PlanID=p2.pvt2PlanID