SQL Pivot table, with multiple pivots on criteria-CodePudding

Here is my dataset,

It has a reservation (unique ID) a reservation_dt a fiscal year (all the same year for the most part) month both numerical and name as well as a reservation status then it has total number reserved followed by a counter (basically 1 for each reservation row)

these are my guidelines (they need to be turned into columns by Month)

Requested - Count of All Distinct reservations
Num_Requested (sum total_number_requested by month)
Booked (count of All Distinct reservations status is order created) Num_Booked (sum total_number_requested by month) where status is order created
Not_Booked (count of All Distinct reservations where status unfulfilled)
Not_Num_Booked, (sum total_number_requested by month where status is unfulfilled)

I am looking to translate this into a pivot table and this is what I've got so far and can't figure out why its not working.

I figured I would turn each of the above guidlines into a column, using either sum(total_number_Requested) or count(total_requested) where reseravation status is ... and such. I'm open to any other ideas of how to make this simpler and make it work.

SELECT [month_name],
       fyear AS fyear,
       Requested,
       Num_Requested
FROM (SELECT reservation,
             reservation_status,
             total_number_requested,
             fyear,
             [month_name],
             [month],
             total_requested
      FROM #temp2) SourceTable
PIVOT (SUM(total_number_requested)
       FOR reservation_status IN ([Requested])) PivotNumbRequested PIVOT(COUNT(reservation)
       FOR total_requested IN ([Num_Requested])) PivotCountRequested
WHERE [month] = 7
ORDER BY fyear,
         [month];

CodePudding user response：

Use conditional expressions to emulate data pivot. Example:

SELECT fyear, Month, Monthname, Count(*) AS CountALL, Sum(total_number_requested) AS TotNum, 
Sum(IIf(reservation_status = "Order Created", total_number_Requested, Null)) AS SumCreated
FROM tablename
GROUP BY fyear, Month, MonthName

More info:
SQLServer - Multiple PIVOT on same columns
Crosstab Query on multiple data points