Month wise totals in columns

Question

Hello,

I've a table (Consumption) as follows:

DocDate   ItemCode    Quantity
01.01.09  A           5
02.01.09  A           6

and so on.. for the whole year

I need results as follow:

ItemCode Jan09Total Feb09Total Mar09Total

The problem is that the quantities should be summed up for the months, which should be in columns and according to the give date criteria, that is, for example from Jan to Mar 09 or Feb to July 09, etc.

How to achieve this using recursive query.

Thanks Rahul

Answer 1

Well, it's not recursive, but this will get you what you want:

SELECT
    DateName(month, DocDate) + ' ' + CAST(YEAR(DocDate) AS VARCHAR(10)) AS MonthYear,
    ItemCode,
    SUM(Quantity)
FROM
    Consumption
GROUP BY
    DateName(month, DocDate) + ' ' + CAST(YEAR(DocDate) AS VARCHAR(10)) AS MonthYear,
    ItemCode
ORDER BY
    1,2

I think recursion would only hinder the performance of this query. Are you sure you want to do that? Or is this just homework?

Answer 2

You can use PIVOT to get the data into columns as you want, however, the list of columns needs to be defined before hand. Therefore you will need to build some dynamic SQL for the columns you require (based on your date range) and then run that to generate your PIVOT.

Answer 3

Assuming you use a DB which supports PIVOT, you should use it. Here's example code for Oracle:

CREATE TABLE Consumption (
  DocDate  DATE,
  ItemCode VARCHAR2(10),
  Quantity NUMBER
);

INSERT INTO Consumption VALUES(to_date('2009-01-01', 'YYYY-MM-DD'), 'A', 5);
INSERT INTO Consumption VALUES(to_date('2009-02-01', 'YYYY-MM-DD'), 'A', 6);
INSERT INTO Consumption VALUES(to_date('2009-01-01', 'YYYY-MM-DD'), 'B', 5);

SELECT * FROM Consumption
PIVOT(
  sum(Quantity)
  FOR DocDate IN (
    to_date('2009-01-01', 'YYYY-MM-DD') AS Jan09Total,
    to_date('2009-02-01', 'YYYY-MM-DD') AS Feb09Total
  )
);

As noted by Robin, if your columns vary, then you will have to dynamically generate such a query with the correct columns.

Answer 4

Here is a link to a wonderfully elegant way of implementing pivot logic on KNOWN x-axis values. Dynamic pivots are more difficult.

I didn't read this article fully, so if it isn't helpful then look up "The Rozenshtein Method"

http://www.stephenforte.net/default.aspx?date=2003-08-07

I've used this in many queries with great results.