4

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,
   [1],
   [2],
   ...
   [1000]
 FROM (SELECT * FROM SalesTable) st
PIVOT
(
AGGREGATE(pk) -- no idea what to use here
FOR pk IN [1],
   [2],
   ...
   [1000]
 );

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?

6

This sounds more like an UNPIVOT to me.

SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value
FROM SalesTable
CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1),
                    ('02'+CAST([Year] AS CHAR(40)), Week2),
                    ('03'+CAST([Year] AS CHAR(40)), Week3),
                    .......)
        CrossApplied (WeekYear, Value)

There are other ways to do an UNPIVOT but using CROSS APPLY is my favorite. I give a more general example here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.