I have been working on this all day and I'm still stuck so let me reset this and hopefully I can clear a few things up. So, starting from scratch ...
At the end of each sales week the manager calls corporate office and says "Hi, this is store 00-0001. Week 51 is over for 2009 and we had $14,000 in sales."
Here is my current schema for that record of information and some sample data from two stores for weeks 51 and 52 in years 2008 and 2009:
CREATE TABLE #salestemp
INSERT INTO #salestemp VALUES (1,'00-0001', 2009, 51, 14000)
INSERT INTO #salestemp VALUES (2,'00-0001', 2009, 52, 15000)
INSERT INTO #salestemp VALUES (3,'00-0002', 2009, 51, 13000)
INSERT INTO #salestemp VALUES (4,'00-0002', 2009, 52, 14000)
INSERT INTO #salestemp VALUES (5,'00-0001', 2008, 51, 9000)
INSERT INTO #salestemp VALUES (6,'00-0001', 2008, 52, 9500)
INSERT INTO #salestemp VALUES (7,'00-0002', 2008, 51, 12000)
INSERT INTO #salestemp VALUES (8,'00-0002', 2008, 52, 13000)
SELECT * FROM #salestemp
What I need to do is create a column for each year and store (So one column for 00-0001's 2009 and another for 00-0001's 2008, and yet another for 00-0002's 2009, and again 00-0002's 2008)
This code works to get me ONE column of data...
SELECT #salestemp.Week, #salestemp.SaleAmmount AS 'Store 00-0001 2009'
WHERE #salestemp.Store = '00-0001' AND #salestemp.Year = '2009'
ORDER BY #salestemp.Week
This is the same screenshot from above that I manually did in excel to hopefully give a better idea of what I'm going for...
I've tried bringing the full table into excel and creating a pivot table there but that isn't working for me because I don't need to sum anything.
I need the data in this presentation format because that is what they are used to looking at, how they want it, and it is the easiest way to compare weeks in each year not only for an individual store but company wide.
I hope this makes sense. I've been wracking my brain on this for a few days now and I'm coming up short.