CODEWITHSUNDEEP
sqloracle-database
Vikram Singh
Vikram Singh

Reputation: 1816

How to get this below output from DUAL in oracle?

1         L            R

1         1            1 
1         1            2
1         1            3
1         2            1
1         2            2
1         2            3
1         3            1
1         3            2
1         3            3

Using this query but not able to get for L column

Select 1,level R
from DUAL
Connect by level <=3

Upvotes: 1

Views: 314

Answers (3)

David Faber
David Faber

Reputation: 12485

You might try something like this:

SELECT 1, CEIL(lvl/3) AS l
     , ROW_NUMBER() OVER ( PARTITION BY CEIL(lvl/3) ORDER BY lvl ) AS r
  FROM (
    SELECT LEVEL AS lvl FROM dual
   CONNECT BY LEVEL <= 9
);

The above avoids a cartesian join. See SQLFiddle here.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could do a Cartesian join in the row generator query you have to generate 3 rows. Thus, the Cartesian product would generate total 9 rows.

For example,

SQL> WITH DATA AS
  2    ( SELECT LEVEL rn FROM dual CONNECT BY LEVEL <=3
  3    )
  4  SELECT 1, A.rn L, b.rn R FROM DATA A, DATA b
  5  /

         1          L          R
---------- ---------- ----------
         1          1          1
         1          1          2
         1          1          3
         1          2          1
         1          2          2
         1          2          3
         1          3          1
         1          3          2
         1          3          3

9 rows selected.

SQL>

Upvotes: 5

Dmitriy
Dmitriy

Reputation: 5565

select 1, L, R
  from (Select level R
          from DUAL
       Connect by level <=3),
       (Select level L
          from DUAL
       Connect by level <=3)

Upvotes: 2

Related Questions