I have read the stuff on MS pivot tables and I am still having problems getting this correct.
I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.
Store Week xCount
------- ---- ------
102 1 96
101 1 138
105 1 37
109 1 59
101 2 282
102 2 212
105 2 78
109 2 97
105 3 60
102 3 123
101 3 220
109 3 87
I would like it to come out as a pivot table, like this:
Store 1 2 3 4 5 6....
-----
101 138 282 220
102 96 212 123
105 37
109
Store numbers down the side and weeks across the top.
Best Answer
If you are using SQL Server 2005+, then you can use the
PIVOT
function to transform the data from rows into columns.It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
First up, here are some quick table definitions and data for use:
If your values are known, then you will hard-code the query:
See SQL Demo
Then if you need to generate the week number dynamically, your code will be:
See SQL Demo.
The dynamic version, generates the list of
week
numbers that should be converted to columns. Both give the same result: