Crosstab / pivot query in Oracle's PL/SQL - iBatis - Extjs and JasperReport

I tried to create a pivot table created from a table in Oracle 10g.

here is the table structure:

CREATE TABLE KOMUNIKA.STOCK_AREA
(
  PRODUCT_CODE  VARCHAR2(20 BYTE)               NOT NULL,
  PRODUCT_NAME  VARCHAR2(50 BYTE),
  AREA_CODE     VARCHAR2(20 BYTE),
  AREA_NAME     VARCHAR2(50 BYTE),
  QUANTITY      NUMBER(20,2)
)

and i need those data displayed as :

Name     US      Europe  Asia    SthAm   Aust    Africa Rest     Total 

C 2601   156     86      437     27      279     22      708     1,715 

C 2605   926     704     7,508   1,947   982     782     1,704   14,553 

Total    56,941  72,891  118,574 55,868  46,758  19,813  60,246  431,091

then i will grab the result using iBatis framework, then display it in a ExtJs Grid, it is really big favour from me, if anyone have same problem as me and want to share it.

i also already find some resource to start:

http://www.sqlsnippets.com/en/topic-12200.html

but if any of you have already find a simpler solution, you will save my weekend :(,

thank you all

Solution

You can do the pivot in SQL itself, using CASE expressions and GROUP BY, as long as the number of columns you want in the result is fixed (you can't write sql that would return a variable number of columns.

Let's say your areas look like this:

AREA_CODE AREA_NAME
--------- ---------
      101 US
      102 Europe
      103 Asia
      104 South America
      105 Australia
      106 Africa
      107 ...
      108 ...

You can write a query that return the results you have above as:

  SELECT PRODUCT_NAME
       , SUM(CASE WHEN AREA_CODE = 101
                  THEN QUANTITY ELSE 0 END) US
       , SUM(CASE WHEN AREA_CODE = 102
                  THEN QUANTITY ELSE 0 END) Europe
       , SUM(CASE WHEN AREA_CODE = 103
                  THEN QUANTITY ELSE 0 END) Asia
       , SUM(CASE WHEN AREA_CODE = 104
                  THEN QUANTITY ELSE 0 END) SthAm
       , SUM(CASE WHEN AREA_CODE = 105
                  THEN QUANTITY ELSE 0 END) Aust
       , SUM(CASE WHEN AREA_CODE = 106
                  THEN QUANTITY ELSE 0 END) Africa
       , SUM(CASE WHEN AREA_CODE NOT IN (101, 102, 103, 104, 105, 106)
                  THEN QUANTITY ELSE 0 END) Rest
       , SUM(QUANTITY) Total
    FROM KOMUNIKA.STOCK_AREA
GROUP BY PRODUCT_NAME;