Back in 2008 I blogged about Pivot/Cross Tab Queries in SQL Server (http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2008/01/21/pivot-cross-tab-queries-in-sql-server.aspx). I was asked for another example so here goes:
--Sample Sales Table
CREATE TABLE [dbo].[Sales](
[ClientID] [int] NOT NULL,
[SalesDate] [datetime] NOT NULL,
[SalesAmount] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
--Added sample data
SELECT [ClientID]
,[SalesDate]
,[SalesAmount]
FROM [Sales]
| ClientID |
SalesDate |
SalesAmount |
| 1 |
1/1/2006 |
100 |
| 1 |
1/1/2007 |
200 |
| 1 |
1/1/2008 |
300 |
| 1 |
1/1/2009 |
400 |
| 2 |
1/1/2006 |
150 |
| 2 |
1/1/2007 |
250 |
| 2 |
1/1/2008 |
250 |
| 2 |
1/1/2009 |
250 |
--Create view to report Sales by Year by Client
SELECT [ClientID]
,DATEPART(yy, [SalesDate]) AS SalesYear
,SUM([SalesAmount]) AS [SalesAmount]
FROM [Sales]
GROUP BY [ClientID]
,DATEPART(yy, [SalesDate])
| ClientID |
SalesYear |
SalesAmount |
| 1 |
2006 |
100 |
| 2 |
2006 |
150 |
| 1 |
2007 |
200 |
| 2 |
2007 |
250 |
| 1 |
2008 |
300 |
| 2 |
2008 |
250 |
| 1 |
2009 |
400 |
| 2 |
2009 |
250 |
--for sql server 2005 +
--Pivot to report sales by client over years
SELECT [ClientID], [2006] AS year2006, [2007] AS year2007, [2008] AS year2008, [2009] AS year2009
FROM
(
SELECT [ClientID], DATEPART(yy, [SalesDate]) AS SalesYear, [SalesAmount]
FROM [Sales]) N
PIVOT
(
SUM ([SalesAmount])
FOR SalesYear IN
(
[2006], [2007], [2008], [2009] )
) AS pvt
ORDER BY [ClientID]
| ClientID |
year2006 |
year2007 |
year2008 |
year2009 |
| 1 |
100 |
200 |
300 |
400 |
| 2 |
150 |
250 |
250 |
250 |