Re: cross-tab querie

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Fri, 26 Jun 1998 21:07:12 GMT
Message-ID: <35940d6f.4683371@www.sigov.si>

On 26 Jun 1998 16:28:27 GMT, "Herve Meftah" <hm89659_at_GlaxoWellcome.co.uk> wrote:

>Hi,
>I need to write a cross-tab querie on ORACLE, I known it's quite simple
>but my SQL is a few rusty, (I've got a better skills on Transact-SQL
>Sybase).
>
>The problem is :
>
>TABLE A
>year period amount
>1990 1 125
>1990 2 130
>1990 3 150
>1991 1 160
>......
>
>Result TABLE
>year periode_1 periode_2 periode 3 total
>1990 125 130 150 405
>1991 160 ....
>
>I guess the syntax it's not the same between ORACLE and SYBASE

In Oracle use the function DECODE:

SQL> SELECT * FROM my_table;

     YEAR PERIOD AMOUNT
--------- --------- ---------

     1990         1       125
     1990         2       130
     1990         3       150
     1991         1       160

SQL> SELECT year,
  2         SUM(DECODE(period,1,amount,0)) periode_1,
  3         SUM(DECODE(period,2,amount,0)) periode_2,
  4         SUM(DECODE(period,3,amount,0)) periode_3,
  5         SUM(amount) total

  6 FROM my_table
  7 GROUP BY year;

     YEAR PERIODE_1 PERIODE_2 PERIODE_3 TOTAL --------- --------- --------- --------- ---------

     1990       125       130       150       405
     1991       160         0         0       160

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Jun 26 1998 - 16:07:12 CDT