I have a table that looks roughly like this.
(Pk int, Year int, Week1, Week2, .., Week53)
The values in each "Week" column are sales data. What I want to do is create a new table from the first table in the form of -
(Pk int, WeekYear, SalesData)
Where "WeekYear" is a concatentation of the year and the week number and SalesData is the same salesdata for that respective field in the former table. (just trying to get the select down for now)
SELECT pk, , , ...  FROM (SELECT * FROM SalesTable) st PIVOT ( AGGREGATE(pk) -- no idea what to use here FOR pk IN , , ...  );
My current attempts make no sense as I'm not sure which aggregate function to apply on my dataset. I'm also having a bit of trouble discerning the best way to create the new column YearWeek.
This seemed like an obvious use for PIVOT (I need to transpose the table). However, I'm having a lot of trouble wrapping my head around how to properly accomplish it. What would be the best way to go about doing this?