Skip to content
Advertisement

Convert Rows to columns using ‘Pivot’ in SQL Server

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.

I would like it to come out as a pivot table, like this:

Store numbers down the side and weeks across the top.

Advertisement

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:

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement