Re: "un-grouping" results

From: G-Man <gfeiner_at_gmail.com>
Date: 3 Feb 2005 18:37:00 -0800
Message-ID: <1107484620.502601.152180@z14g2000cwz.googlegroups.com>

Using the link to asktom - decided to try to answer the question in detail. Here is what I came up with:

SQL> create table exp (id varchar2(2), num number);

Table created.

SQL> insert into exp values ('A',2);

1 row created.

SQL> insert into exp values ('B',4);

1 row created.

SQL> insert into exp values ('C',5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from exp;

ID NUM
-- ----------

A           2
B           4
C           5

Now the query =>

  1 select *
  2 from ( select rownum r

  3             from ( select 1
  4                      from dual group by cube (1,1,1,1,1,1))) a,
  5          exp

  6* where exp.num >= a.r
SQL> /          R ID NUM
---------- -- ----------

         1 A           2
         2 A           2
         1 B           4
         2 B           4
         3 B           4
         4 B           4
         1 C           5
         2 C           5
         3 C           5
         4 C           5
         5 C           5

11 rows selected.

Only caveat here is to make sure that the cube (1,1,1,1,1,1) will return more rows then you every would need (2 to the nth - or 2 to the sixth in this case). If you don't know that, then you would need to look into the pipeline function. I'm gonna check that out as well ;-) Received on Thu Feb 03 2005 - 20:37:00 CST