Re: SQL-Question: Transformation of a column

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 3 Nov 2003 17:06:41 GMT
Message-ID: <bo61v0$17m8fm$1@ID-82536.news.uni-berlin.de>

> Hi,
>
> in a multi-company database (Oracle 9i Release 2, Enterprise
> Edition), we've got some statistical views
> telling us the relevant numbers in the following form:
>
> name period value
> company1 2003-7 x1
> company1 2003-8 x2
> company2 2003-7 x3
> ...
>
> What our management now would like to see is the following:
>
> name 2003-7 2003-8 ... Total
> company1 x1 x2 x1+x2
> company2 x3 x3
>
> Other than to export the results and make a Pivot-Table
> in Excel, we currently don't see a solution.
>
> Is there a statement variation to solve this?

Olaf,
try a variation of the following:

drop table t_;

create table t_ (
  nm Varchar2(20),
  pr Char ( 7),
  vl Number
);

insert into t_ values ('company 1','2003-06', 10);
insert into t_ values ('company 1','2003-07', 29);
insert into t_ values ('company 1','2003-08', 39);
insert into t_ values ('company 1','2003-09', 41);
insert into t_ values ('company 1','2003-10', 22);

insert into t_ values ('company 2','2003-06', 13);
insert into t_ values ('company 2','2003-07', 17);
insert into t_ values ('company 2','2003-08', 61);

insert into t_ values ('company 2','2003-09', 55); insert into t_ values ('company 2','2003-10', 71);

insert into t_ values ('company 3','2003-06', 33);
insert into t_ values ('company 3','2003-07', 18);
insert into t_ values ('company 3','2003-08', 27);
insert into t_ values ('company 3','2003-09',  5);
insert into t_ values ('company 3','2003-10', 32);

select
  nm,

  jul,
  aug,
  sep,

  jul+aug+sep "Total"
from (
  select
    nm,

    max(case when pr='2003-07' then vl else null end) jul,
    max(case when pr='2003-08' then vl else null end) aug,
    max(case when pr='2003-09' then vl else null end) sep

  from
    t_
  group by
    nm);

Also, have a look at
http://www.adp-gmbh.ch/ora/plsql/pipeline.html#pivot for an idea using pipelined functions.

hth
Rene   

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Mon Nov 03 2003 - 11:06:41 CST