Thursday 15 January 2009

Creating a simple Pivot Table example

This has been driving me batty for hours. I finally managed to get one working. Here's what I did.

Firstly for this example I created a time table here's the data. As you can see it's a simple table showing year,town and an amount, this is the source data which is in a table I've called "felbrigg".

year,town,amount
1994,towcestor,1
1994,towcestor,2
1995,towcestor,4
1994,mk,8
1994,mk,16
1995,mk,32
1996,mk,64
1994,barley,128
1994,barley,256
1995,barley,512

Now here is what I'm trying to achieve.

year,towcestor,mk,barley
1994,3,24,384
1995,4,32,512
1996,NULL,64,NULL

And here is the pivot statement to do it. with line numbers to help explain

1
SELECT [year],[towcestor],[mk],[barley]
2
FROM (SELECT [year],town,amount from felbrigg) as source
3
pivot
4
(
5
sum(amount)
6
for town in ([towcestor],[mk],[barley])
7
) as pvt



1. Is the column list in the final output

2. Is the source of data, matches my source table in this example.

5. Is the calculation that is to appear in each cell of the final results

6. This specifies the column in the source that will be turned into columns in the final result, and you have to hardcode the values you want from the source column!