Skip to Main Content
  • Questions
  • Need to generate the output in incremental loops

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Santhosh.

Asked: September 08, 2017 - 7:15 am UTC

Last updated: September 14, 2017 - 3:24 am UTC

Version: 11.2.04

Viewed 1000+ times

You Asked

Hi Tom,

I have a query which returns the below output.

ONE
TWO
THREE

I need to get the out as

ONE
ONE
TWO
ONE
TWO
THREE
ONE
TWO
THREE
FOUR

i.e like incremental sets (by step 1).

ONE

ONE
TWO

ONE
TWO
THREE

ONE
TWO
THREE
FOUR

Please help.



CREATE TABLE "HR"."TABLE2"
( "COLUMN1" VARCHAR2(20 BYTE)
);

Insert into TABLE2 (COLUMN1) values ('ONE');
Insert into TABLE2 (COLUMN1) values ('TWO');
Insert into TABLE2 (COLUMN1) values ('THREE');
Insert into TABLE2 (COLUMN1) values ('four');



with LiveSQL Test Case:

and Connor said...

SQL> create table t ( id int, col varchar2(10));

Table created.

SQL>
SQL> Insert into t  values (1,'ONE');

1 row created.

SQL>
SQL> Insert into t values (2,'TWO');

1 row created.

SQL>
SQL> Insert into t values (3,'THREE');

1 row created.

SQL>
SQL> Insert into t values (4,'FOUR');

1 row created.

SQL>
SQL> select * from t t1, t t2
  2  where t1.id <= t2.id;

        ID COL                ID COL
---------- ---------- ---------- ----------
         1 ONE                 1 ONE
         1 ONE                 2 TWO
         1 ONE                 3 THREE
         1 ONE                 4 FOUR
         2 TWO                 2 TWO
         2 TWO                 3 THREE
         2 TWO                 4 FOUR
         3 THREE               3 THREE
         3 THREE               4 FOUR
         4 FOUR                4 FOUR

10 rows selected.

SQL>
SQL>
SQL> select t1.col
  2  from t t1, t t2
  3  where t1.id <= t2.id
  4  order by t2.id, t1.id;

COL
----------
ONE
ONE
TWO
ONE
TWO
THREE
ONE
TWO
THREE
FOUR


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great!!! Thanks a lot

A reader, September 08, 2017 - 9:46 am UTC

This is what i exactly wanted and you made it so simple!!!
Connor McDonald
September 11, 2017 - 8:19 am UTC

glad we could help

My 2 cents

GJ, September 08, 2017 - 5:44 pm UTC

First i generate the set of rows which i need using dual and connect by and name that column as x.

Then i cross join the same table with the condition such that b.x<=a.x(Same thing as Conors solution)

Finally i perform a "spell a number" using Julian date conversion.
(Its a cool thing to know this check this link
http://www.oracle.com/technetwork/issue-archive/2006/06-jul/o46asktom-085866.html
)
select a.x,b.x,to_char(to_date(b.x,'J'),'JSP')
  from (select level as x from dual connect by level<=4) a
  join (select level as x from dual connect by level<=4) b
    on  b.x<=a.x   
order by a.x,b.x

Connor McDonald
September 11, 2017 - 8:27 am UTC

nice work

model clause

Rajeshwaran, Jeyabal, September 10, 2017 - 8:33 am UTC

demo@ORA12C> select x, initcap(to_char(to_date(x,'j'),'jsp')) as col
  2  from t
  3  model
  4     partition by(id)
  5     dimension by (1 x )
  6     measures( col, 0 y,0 z)
  7     rules(
  8             z[1] = cv(id) ,
  9             y[ for x from 1 to z[1]
 10                     increment 1 ] = cv(x)
 11     )
 12  order by id,y
 13  /

         X COL
---------- -----------------------------------------------------------
         1 One
         1 One
         2 Two
         1 One
         2 Two
         3 Three
         1 One
         2 Two
         3 Three
         4 Four

10 rows selected.

demo@ORA12C>

A reader, September 11, 2017 - 12:51 pm UTC


If you want to avoid the sort ;)

Stew Ashton, September 11, 2017 - 5:51 pm UTC

var n number;
exec :n := 4;
select to_char(to_date(
  level - ceil((sqrt(8*level + 1) -1)/2) * (ceil((sqrt(8*level + 1) -1)/2) -1) / 2
,'J'),'JSP') col
from dual
connect by level <= :n * (:n+1) / 2;

COL                                                                           
-------
ONE
ONE
TWO
ONE
TWO
THREE
ONE
TWO
THREE
FOUR

I dare you to put that into production ;)

Best regards, Stew
Connor McDonald
September 13, 2017 - 7:21 am UTC

:-)

@Stew If you want to avoid the sort ;)

GJ, September 13, 2017 - 7:31 am UTC

Just awesome!

Can you please explain step by step how the query works using ceil and sqrt

@GJ from Kochi: explanation

Stew Ashton, September 13, 2017 - 10:56 am UTC

Let's start with a constant 3 instead of :n.

The sum of consecutive integers from 1 through n can be calculated as n(n+1)/2. This gives me the number of rows to generate:
select level S
from dual
connect by level <= 3 * (3 +1) / 2;

         S
----------
         1
         2
         3
         4
         5
         6

Now what I need is to calculate n based on the value of S. With a little help from my son, I found the formula n = (sqrt(8*S+1)-1)/2.
select level S,
(sqrt(8*level+1)-1)/2 n
from dual
connect by level <= 3 * (3 +1) / 2;

         S          N
---------- ----------
         1          1
         2 1.56155281
         3          2
         4 2.37228132
         5 2.70156212
         6          3
If we round that up, what we get is actually the previous "n+1"
select level S,
ceil((sqrt(8*level+1)-1)/2) "Prev n+1"
from dual
connect by level <= 3 * (3 +1) / 2;

         S   Prev n+1
---------- ----------
         1          1
         2          2
         3          2
         4          3
         5          3
         6          3

So now we can calculate the previous "n(n+1)/2". We subtract that from our current S and we get a remainder, which is our answer.
select level S,
ceil((sqrt(8*level+1)-1)/2) "Prev n+1",
ceil((sqrt(8*level+1)-1)/2)-1 "Prev n",
ceil((sqrt(8*level+1)-1)/2) * (ceil((sqrt(8*level+1)-1)/2)-1)/2 "Prev n(n+1)/2",
level - ceil((sqrt(8*level+1)-1)/2) * (ceil((sqrt(8*level+1)-1)/2)-1)/2 "S - Prev n(n+1)/2"
from dual
connect by level <= 3 * (3 +1) / 2;

         S   Prev n+1     Prev n Prev n(n+1)/2 S - Prev n(n+1)/2
---------- ---------- ---------- ------------- -----------------
         1          1          0             0                 1
         2          2          1             1                 1
         3          2          1             1                 2
         4          3          2             3                 1
         5          3          2             3                 2
         6          3          2             3                 3
Best regards, Stew
Connor McDonald
September 14, 2017 - 3:24 am UTC

nice step through explanation

Thanks Stew

George, September 13, 2017 - 4:12 pm UTC

Thanks Stew. Clever !

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.