Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

 FROM (SELECT * FROM SalesTable) st
AGGREGATE(pk) -- no idea what to use here
FOR pk IN [1],

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?

share|improve this question
up vote 5 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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