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

Question

Hi all,

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 realy 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

Answer 1

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;