Splitting data into a per-year basis using SQL with pivots

I'm having some trouble figuring out how construct a series of SQL statements to split some data I have into a per-year basis. There are n items which have a recorded quantity for a set of days across so many years. Here's is a a small sample layout for the main data I have.

   |========|==============|===========|
   | Name   | Date         | Quantity  |
   |========|==============|===========|
 1 | AAAAA  | 10-DEC-2008  | 5         |
 2 | AAAAA  | 11-DEC-2008  | 2         |
 3 | AAAAA  | 12-DEC-2008  | 0         |
 4 | AAAAA  | 09-DEC-2009  | 3         |
 5 | AAAAA  | 10-DEC-2009  | 2         |
 6 | AAAAA  | 11-DEC-2009  | 3         |
 7 | BBBBB  | 10-DEC-2008  | 5         |
 8 | BBBBB  | 11-DEC-2008  | 2         |
 9 | BBBBB  | 12-DEC-2008  | 0         |
10 | BBBBB  | 09-DEC-2009  | 3         |
11 | BBBBB  | 10-DEC-2009  | 1         |
12 | BBBBB  | 11-DEC-2009  | 0         |
   |========|==============|===========|

I need to convert this into a table which takes the form.

   |========|==============|===============|===============|
   | Name   | Date         | Quantity 2008 | Quantity 2009 |
   |========|==============|===============|===============|
 1 | AAAAA  | 09-DEC       |               | 3             |
 2 | AAAAA  | 10-DEC       | 5             | 2             |
 3 | AAAAA  | 11-DEC       | 2             | 3             |
 4 | AAAAA  | 12-DEC       | 0             |               |
 5 | BBBBB  | 09-DEC       |               | 3             |
 6 | BBBBB  | 10-DEC       | 5           开发者_如何学Go  | 1             |
 7 | BBBBB  | 11-DEC       | 2             | 0             |
 8 | BBBBB  | 12-DEC       | 0             |               |
   |========|==============|===============|===============|

It should be assumed that there are thousands of named items in the database and the start days of recording on each year will be different, E.G., 6th Dec 2010 and 5th Dec 2009.

Hope someone can help.

In case you are using Oracle 11g, you can pivot the table as:

select *
  from (    
    select name,
           to_char(theDate, 'DD-MON') as dayMonth,
           extract(year from thedate) as year,
           quantity
      from yourTable
) 
 pivot (sum(quantity) as quantity for (year) in (2008 as y2008, 2009 as y2009))
order by name, dayMonth;

that returns the following

NAME  DAYMONTH        Y2008_QUANTITY         Y2009_QUANTITY         
----- --------------- ---------------------- ---------------------- 
AAAAA 09-DEC                                 3                      
AAAAA 10-DEC          5                      2                      
AAAAA 11-DEC          2                      3                      
AAAAA 12-DEC          0                                        
BBBBB 09-DEC                                 3                      
BBBBB 10-DEC          5                      1                      
BBBBB 11-DEC          2                      0                      
BBBBB 12-DEC          0                                           

8 rows selected

Prior to Oracle11, you can use this standard pivot trick:

SQL> create table mytable (name,mydate,quantity)
  2  as
  3  select 'AAAAA', date '2008-12-10', 5 from dual union all
  4  select 'AAAAA', date '2008-12-11', 2 from dual union all
  5  select 'AAAAA', date '2008-12-12', 0 from dual union all
  6  select 'AAAAA', date '2009-12-09', 3 from dual union all
  7  select 'AAAAA', date '2009-12-10', 2 from dual union all
  8  select 'AAAAA', date '2009-12-11', 3 from dual union all
  9  select 'BBBBB', date '2008-12-10', 5 from dual union all
 10  select 'BBBBB', date '2008-12-11', 2 from dual union all
 11  select 'BBBBB', date '2008-12-12', 0 from dual union all
 12  select 'BBBBB', date '2009-12-09', 3 from dual union all
 13  select 'BBBBB', date '2009-12-10', 1 from dual union all
 14  select 'BBBBB', date '2009-12-11', 0 from dual
 15  /

Table created.

SQL> select name
  2       , to_char(mydate,'dd-MON')
  3       , sum(case extract(year from mydate) when 2008 then quantity end) quantity_2008
  4       , sum(case extract(year from mydate) when 2009 then quantity end) quantity_2009
  5    from mytable
  6   group by name
  7       , to_char(mydate,'dd-MON')
  8   order by name
  9       , to_char(mydate,'dd-MON')
 10  /

NAME  TO_CHA QUANTITY_2008 QUANTITY_2009
----- ------ ------------- -------------
AAAAA 09-DEC                           3
AAAAA 10-DEC             5             2
AAAAA 11-DEC             2             3
AAAAA 12-DEC             0
BBBBB 09-DEC                           3
BBBBB 10-DEC             5             1
BBBBB 11-DEC             2             0
BBBBB 12-DEC             0

8 rows selected.

Regards,
Rob.

Something like this should do the job:

SELECT
    Name, FormattedDate, SUM(Quantity_2008), SUM(Quantity_2009)
FROM
(
SELECT
    Name,
    ConvertDateToDayMonthRepresentation(YourDate) as FormattedDate,
    DECODE(EXTRACT(YEAR FROM YourDate), 2008, Quantity, 0) as Quantity_2008, 
    DECODE(EXTRACT(YEAR FROM YourDate), 2009, Quantity, 0) as Quantity_2009
FROM
    YourTable
)
GROUP BY Name, FormattedDate;

Please note:

1. You need to add code for each year you want to have - your Query can't have dynamic columns.
2. ConvertDateToDayMonthRepresentation is a user defined function that should convert a date into the string representation of day and month without the year. It is trivial to create using EXTRACT.