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.
Comments
Post a Comment