Home » SQL & PL/SQL » SQL & PL/SQL » explose rows (9208)
explose rows [message #570832] Fri, 16 November 2012 05:20 Go to next message
Amine
Messages: 255
Registered: March 2010
Senior Member

Hi all,
create table exp_rows
(
id int ,
number_of_id int
)
/

insert into exp_rows values (1, 3);
insert into exp_rows values (2, 4);
insert into exp_rows values (3, 5);


I want to query exp_rows to get this :

id
--
1
1
1
2
2
2
2
3
3
3
3
3


Thanks in advance,

Amine
Re: explose rows [message #570834 is a reply to message #570832] Fri, 16 November 2012 05:40 Go to previous messageGo to next message
Littlefoot
Messages: 19294
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> select * from exp_rows;

        ID NUMBER_OF_ID
---------- ------------
         1            3
         2            4
         3            5

SQL> select id
  2  from exp_rows,
  3       table(cast(multiset(select level
  4                           from dual
  5                           connect by level <= number_of_id) as sys.odcinumberlist))
  6  order by id;

        ID
----------
         1
         1
         1
         2
         2
         2
         2
         3
         3
         3
         3
         3

12 rows selected.

SQL>
Re: explose rows [message #570835 is a reply to message #570834] Fri, 16 November 2012 05:45 Go to previous messageGo to next message
Amine
Messages: 255
Registered: March 2010
Senior Member

Wow !
SQL is so amazing !
Thanks litleFoot !
Re: explose rows [message #570841 is a reply to message #570835] Fri, 16 November 2012 06:14 Go to previous message
Michel Cadot
Messages: 58482
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For all row generation queries and many examples (including this question), see row generator topic.

Regards
Michel
Previous Topic: inserting more than 30000 records into a table
Next Topic: dequeue_array not working with array_size > 1 [merged 2 by jd]
Goto Forum:
  


Current Time: Tue Jul 22 11:22:58 CDT 2014

Total time taken to generate the page: 0.10930 seconds