[SQL] Pivot multiple columns in SQL Server

I have read Microsoft SQL Server docs about pivot feature and I sort of get the gist of how it works - but there is a bit of curve ball which I am unable to solve.

I have 4 tables, each contains orders info, invoice info, calculations, payments and from my understanding they would be considered un-pivoted.

After reading SQL Server Pivot docs, I put this query together which gives me a pivot of Tag column values.

For simplicity I joined only two out of 4 tables: Orders and Calculations and applied PIVOT()

SELECT *
FROM
(SELECT
OD.Date,
OD.TicketNumber,
OD.Tag,
OD.SubTotal,
TC.[Name] AS Calculation,
TC.Amount
FROM
@OrderData OD
LEFT JOIN
@TicketCalculations TC ON TC.TicketId = OD.TicketId
GROUP BY
OD.Date, OD.TicketNumber,
OD.Tag, OD.SubTotal, OD.TicketTotal,
TC.Name, TC.Amount) AS src
PIVOT
(SUM(src.SubTotal)
FOR Tag IN (Food, Beer, Cocktail, Other)
) AS piv1

Date TicketNumber Calculation Amount Food Beer Cocktail Other
2024-09-27 5 Round -25.00 - 38.00 - -
2024-09-27 6 Delivery Commission -5.03 13.50 20.00 - -
2024-09-27 6 Discount -2.85 13.50 20.00 - -
2024-10-01 7 - - - 2.00 - -
2024-10-01 8 - - - - 4.00 -
2024-10-01 9 - - 8.50 - - -

However, I also want to pivot Calculations values with amounts followed by other tables.

This is my expected output:

Date TicketNumber Food Soda Beer Cocktails Other SubTotal Round Discount Delivery Commission Total Cash QR Card Voucher etc.
Sep-27 5 - - 38 - - 38 -25 - - 13 13 - - - -
Sep-27 6 13.5 - 20 - - 33.5 - -2.85 -5.03 25.62 20 5.62 - - -
Sep-27 7 - - 2 - - 2 - - - 2 - - - - -
Oct-01 8 - - - 4 - 4 - - - 4 - - - - -
Oct-01 9 8.5 - - - - 8.5 - - - 8.5 - - - - -

Is there a way I can insert pivot table out into another variable table or temporary table? To JOIN with another which I will be pivoting? Or is there another way of achieving this?

I won't shy away from restructuring initial query if needed too.

Continue reading...