SQL Pivot table, with multiple pivots on criteria

Question

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];

Honestly, I would recommend looking at conditional aggregation over the far more restrictive PIVOT operator; you may well find it a lot easier. — Thom A
– Thom A ♦, Commented Jan 21, 2022 at 16:32
If you supply consumable sample data (not an image) and your expected results, that will also greatly help others help you. — Thom A
– Thom A ♦, Commented Jan 21, 2022 at 16:34
Why do fields with numeric data appear to be text type? I would remove "fy" from fyear data and make this a number field. — June7
– June7, Commented Jan 22, 2022 at 7:26

June7 · Accepted Answer · 2022-01-22 07:28:19Z

1

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

edited Jan 22, 2022 at 7:28

answered Jan 22, 2022 at 3:22

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Pivot table, with multiple pivots on criteria

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related