How to cross join with out using a table?-CodePudding

Recently, I am trying to create a table. I have a column that contains 'a', 'b', 'c' and would like to cross join it with 1,2,3 in to the table below.

However, I don't have a table that contains values 1,2,3 and need to do it without creating a table.

Can I achieve this without creating any table? Thanks a lot!

Col1
a
b
c

Col1 Col2
a   1
b   1
c   1
a   2
b   2
c   2
a   3
b   3
c   3

CodePudding user response：

Use a CTE instead:

SQL> with
  2  a (col) as
  3    (select 'a' from dual union all
  4     select 'b' from dual union all
  5     select 'c' from dual
  6    ),
  7  b (col) as
  8    (select 1 from dual union all
  9     select 2 from dual union all
 10     select 3 from dual
 11    )
 12  select a.col, b.col
 13  from a cross join b;

C        COL
- ----------
a          1
a          2
a          3
b          1
b          2
b          3
c          1
c          2
c          3

9 rows selected.

SQL>

CodePudding user response：

You can use:

SELECT *
FROM   table1
       CROSS JOIN (SELECT LEVEL AS col2 FROM DUAL CONNECT BY LEVEL <= 3);

WITH data (col1, col2) AS (
  SELECT col1, 1 FROM table1
UNION ALL
  SELECT col1, col2   1 FROM data WHERE col2 < 3
)
SELECT * FROM data;

Which, given your sample data:

CREATE TABLE table1 (col1) AS
SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL;

Both output:

COL1 COL2

a 1

b 1

c 1

a 2

b 2

c 2

a 3

b 3

c 3

COL1	COL2
a	1
b	1
c	1
a	2
b	2
c	2
a	3
b	3
c	3

db<>fiddle here