I have a few tables that need to be converted into a single new table. I'm positive (un)pivot is the solution to the problem. However, I'm uncertain what the best way is to unpivot data from multiple tables into a single table with a single query.
The tables look roughly like this:
Sales: Pk, Year, Week1, Week2, Week3,...Week52
Quantity: Pk, Year, Week1, Week2, Week3,...Week52
Orders: Pk, Year, Week1, Week2, Week3,...Week52
... And so on
What I want is a table in the format
Aggregate table: Pk, Year_Week, Sales, Quantity, Orders
I've already learned from a previous question (Single year column and multiple weeks columns into single year/week column with PIVOT) how to do this for a single table. Which looks roughly like this:
SELECT Pk, Sales FROM
(SELECT Pk, Wk1, Wk2, ..., Wk52
FROM Sales_Table) source
FOR Sales IN Wk1, Wk2, ..., Wk52
How can I do this for multiple tables in one query?