Re: about extending query

From: Erika Grondzakova <Erika.Grondzakova_at_cern.ch>
Date: Fri, 25 Jun 1999 09:20:45 +0200
Message-ID: <37732DCD.C4F53753@cern.ch>

Hi Charles,

One solution could be that you create a simple table like

SQL> create table foo(id number(10));

And you fill it with so many rows (the values really doesn't matter) how is the biggest value in TOTAL column in your table.

So in example you wrote, it would be 4 rows.

And then select could be like this :

SQL> select a.name, 
  2         a.total, 
  3         b.sn  

  4 from
  5 (select name, total from your_table_name) a,   6 (select ROWNUM sn from foo) b
  7 where b.sn <= a.total
  8 order by a.name, b.sn;

NAME TOTAL SN
---------- --------- ---------

cmpA               4         1
cmpA               4         2
cmpA               4         3
cmpA               4         4
cmpB               3         1
cmpB               3         2
cmpB               3         3

HTH, Erika

CL wrote:
>
> Hi there
>
> I have a case need to extend a table like this
> name total
> cmpA 4
> cmpB 3
>
> into the table like this
>
> name total sn
> cmpA 4 1
> cmpA 4 2
> cmpA 4 3
> cmpA 4 4
> cmpA 3 1
> cmpA 3 2
> cmpA 3 3
>
> Can any noe help? thanks in advance.
>
> Charles
Received on Fri Jun 25 1999 - 02:20:45 CDT