Oracle select from dual with multiple rows and columns

Question

I need to join against a dynamic list of numbers that I retrieve from my program dynamically. The number of rows is not fixed, nor are the numbers that are used.

I am not finding a better way to accomplish this than with the following (for my purposes, a temporary table is not helpful):

select 111 as col1, 322 as col2 from dual
union all
select 3 as col1, 14 as col2 from dual
union all
select 56 as col1, 676 as col2 from dual;

Is there a better way to do this? I see that there is a connect by statement that can return multiple rows, but I'm not seeing a way to do multiple rows and columns.

Answer 1

You can use the CONNECT BY here with a little math:

SELECT Level * 2 - 1 AS col1, Level * 2 AS col2
FROM DUAL
CONNECT BY LEVEL <= 3;

That will give you your example of three rows. Adjust the LEVEL <= ... value to get more rows.

---

Update 2022: I see that the OP edited the question the day after my answer, making it invalid.

B. Khan's answer a year later is more on point, though honestly I prefer CASE over DECODE -- DECODE is more compact but IMO CASE is more readable. I also prefer LEVEL over ROWNUM here because ROWNUM doesn't always work the way one would expect.

SELECT
  CASE LEVEL
    WHEN 1 THEN 111
    WHEN 2 THEN 3
    WHEN 3 THEN 56
    END as col1,
  CASE LEVEL
    WHEN 1 THEN 322
    WHEN 2 THEN 14
    WHEN 3 THEN 676
    END as col2
FROM DUAL
CONNECT BY LEVEL <= 3;

Answer 2

You can use the decode and connect by level:

select decode(rownum, 1, 111, 2, 3, 3, 56) as col1,
       decode(rownum, 1, 322, 2, 14, 3, 676) as col2
  from dual
connect by level <= 3;