Home » SQL & PL/SQL » SQL & PL/SQL » Repeat data based on count column (Oracle 11g Release 2, Linux)
Repeat data based on count column [message #654745] Thu, 11 August 2016 10:42 Go to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi All,

I want to display ID column value based count value
Below is the example,

WITH J
     AS (select 'A123' ID, (SYSDATE+3 - SYSDATE) CNT FROM DUAL UNION 
         select 'A1234' ID, (SYSDATE+2 - SYSDATE) CNT FROM DUAL UNION
         select 'A1235' ID, (SYSDATE+1 - SYSDATE) CNT FROM DUAL)
select ID from j   connect by LEVEL<=CNT ;


Expected output

ID
----
A123
A123
A123
A1234
A1234
A1235

Please help me to get above result set.

Thanks
Re: Repeat data based on count column [message #654756 is a reply to message #654745] Thu, 11 August 2016 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/493455/#msg_493455

Re: Repeat data based on count column [message #654762 is a reply to message #654745] Thu, 11 August 2016 23:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> WITH
  2    J AS
  3  	 (select 'A123' ID, (SYSDATE+3 - SYSDATE) CNT FROM DUAL UNION
  4  	  select 'A1234' ID, (SYSDATE+2 - SYSDATE) CNT FROM DUAL UNION
  5  	  select 'A1235' ID, (SYSDATE+1 - SYSDATE) CNT FROM DUAL)
  6  select j.id
  7  from   j,
  8  	    table
  9  	      (cast
 10  		 (multiset
 11  		    (select rownum from dual
 12  		     connect by level <= j.cnt)
 13  		  as sys.odcinumberlist)) t
 14  /

ID
-----
A123
A123
A123
A1234
A1234
A1235

6 rows selected.
Re: Repeat data based on count column [message #654766 is a reply to message #654762] Fri, 12 August 2016 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is this not what there is in the link I posted?

Re: Repeat data based on count column [message #654769 is a reply to message #654766] Fri, 12 August 2016 02:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Thu, 11 August 2016 23:28

Is this not what there is in the link I posted?

There was something similar there among various methods. I just demonstrated my favorite method specific to the op's data.
Re: Repeat data based on count column [message #654779 is a reply to message #654769] Fri, 12 August 2016 07:23 Go to previous message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

Thanks Michael and Barbara


Previous Topic: Dynamic Update statement Error
Next Topic: Help reverse engineering pl sql query
Goto Forum:
  


Current Time: Tue Dec 11 14:20:49 CST 2018