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
UNPIVOT
(
FOR Sales IN Wk1, Wk2, ..., Wk52
) upt
How can I do this for multiple tables in one query?
Best Answer
I don't have data on what links your tables so you might have to do a very little change to the ON clauses, but here it is:
Creating tables with data
And the query: