Pivoting in SQL Server 2000

In one of the discussion forums, one guy asked the following question:

Here is the Table:

Id	SalesDate	Sales
1	2 Jan 2006	100
1	13 Jan 2006	200
1	14 Feb 2006	300
2	3 Feb 2006	150
2	4 Feb 2006	200
3	5 Jan 2006	300
3	13 Feb 2006	400

I need the following output:

Id	Jan	Feb	Mar	Apr
1	300	300	0	0
2	0	350	0	0
3	300	400	0	0

Solution:

Let’s create a table

CREATE TABLE PivotEx (Id INT, SalesDate DATETIME, Sales MONEY)

Next, Insert values

INSERT INTO PivotEx VALUES (1,'2 Jan 2006',100)

INSERT INTO PivotEx VALUES (1,'13 Jan 2006',200)

INSERT INTO PivotEx VALUES (1,'14 Feb 2006',300)

INSERT INTO PivotEx VALUES (2,'3 Feb 2006',150)

INSERT INTO PivotEx VALUES (2,'4 Feb 2006',200)

INSERT INTO PivotEx VALUES (3,'5 Jan 2006',300)

INSERT INTO PivotEx VALUES (3,'13 Feb 2006',400)

Here is the query:

SELECT Id, SUM(CASE WHEN MONTH(SalesDate) = 1 THEN Sales ELSE 0 END) AS 'Jan',

SUM(CASE WHEN MONTH(SalesDate) = 2 THEN ISNULL(Sales,0) ELSE 0 END) AS 'Feb',

SUM(CASE WHEN MONTH(SalesDate) = 3 THEN ISNULL(Sales,0) ELSE 0 END) AS 'Mar',

SUM(CASE WHEN MONTH(SalesDate) = 4 THEN ISNULL(Sales,0) ELSE 0 END) AS 'Apr'

FROM PivotEx

GROUP BY Id

CASE statement has to be extended for each and every month.