Re: HELP SQL GURUS

From: Yasuhiro Ushitaki <ushi_at_po.globe.or.jp>
Date: 1996/12/22
Message-ID: <01bbf00b$f7bd37a0$LocalHost_at_dynabook>#1/1

Hi,

Tim Rogers <timr_at_dircon.co.uk> wrote in article <32B8FA97.1937_at_dircon.co.uk>...
> Brian Spears wrote:
> >
> > Hi,
> >
> > I have a simple join on two table to produce a/view or table
> > with the following rows.
> >
> > id Desc
> > 88 a
> > 88 b
> > 88 c
> > 90 a
> > 90 b
> > 91 d
> >
> > Under time pressure I have to build a view to TRANSPOSE
> > the rows and colums like this
> >
> > id desc1 desc2 desc3 ... desc7
> > 88 a b c NULL
> > 90 a b NULL NULL
> > 91 d NULL NULL NULL
> >
> > There is a maximum of only using seven columns.
> >
>
> The nearest I can get is a view which will give you:
>
> id desc1 desc2 desc3 desc4 ... desc7
> 88 a b c NULL NULL
> 90 a b NULL NULL NULL
> 91 NULL NULL NULL d NULL
>
> i.e. desc1 will only contain a or NULL, desc2 b or NULL, etc.
>
> Here goes:
>
> CREATE OR REPLACE
> VIEW timbos_view
> AS
> SELECT id,
> MIN (DECODE (desc, 'a', desc, NULL)) desc1,
> MIN (DECODE (desc, 'b', desc, NULL)) desc2,
> MIN (DECODE (desc, 'c', desc, NULL)) desc3,
> MIN (DECODE (desc, 'd', desc, NULL)) desc4,
> MIN (DECODE (desc, 'e', desc, NULL)) desc5,
> MIN (DECODE (desc, 'f', desc, NULL)) desc6,
> MIN (DECODE (desc, 'g', desc, NULL)) desc7
> FROM brians_view
> GROUP
> BY id

I think Tim's soln has good performance.

Following is another solns, but this soln has disadvantage on performance.

SQL> desc desc0;

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER(2)
 DESC0                                    CHAR(1)

SQL> select * from desc0;

       ID D
--------- -

       88 a
       88 b
       88 c
       90 a
       90 b
       91 d

6 rows selected.

SQL> create or replace view desc1
  2 as select d1.id,
  3 min(d1.desc0) desc1
  4 from desc0 d1
  5 group by d1.id;

View created.

SQL> create or replace view desc2
  2 as select d2.id,
  3 d1.desc1,
  4 min(decode(d2.desc0,d1.desc1,null,d2.desc0)) desc2   5 from desc1 d1, desc0 d2
  6 where d1.id = d2.id
  7 group by d2.id, d1.desc1;

View created.

SQL> create or replace view desc3
  2 as select d3.id,
  3 d2.desc1,
  4 d2.desc2,
  5 min(decode(d3.desc0,d2.desc1,null,d2.desc2,null,d3.desc0)) desc3   6 from desc2 d2, desc0 d3
  7 where d2.id = d3.id
  8 group by d3.id, d2.desc1, d2.desc2;

View created.

SQL> create or replace view desc4
  2 as select d4.id,

  3  d3.desc1,
  4  d3.desc2,
  5  d3.desc3,
  6  min(decode(d4.desc0,d3.desc1,null,d3.desc2,null,d3.desc3,null,
  7             d4.desc0)) desc4

  8 from desc3 d3, desc0 d4
  9 where d3.id = d4.id
 10 group by d4.id, d3.desc1, d3.desc2, d3.desc3;

View created.

SQL> select * from desc4;

       ID D D D D
--------- - - - -

       88 a b c
       90 a b
       91 d

CREATE view desc5, desc6, desc7 as so on.

-- 
Yasuhiro Ushitaki / Toyo Information Systems Co.,Ltd.

Received on Sun Dec 22 1996 - 00:00:00 CET