0

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
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
aukk123
  • 311
  • 3
  • 12
  • Does this answer your question? [SQL to generate a list of numbers from 1 to 100](https://stackoverflow.com/questions/2847226/sql-to-generate-a-list-of-numbers-from-1-to-100) – astentx Oct 21 '21 at 11:25

3 Answers3

2

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • sorry may I know is the table dual should exist on by server? because on my server, I dont have a table that contains 1,2,3. Thanks! – aukk123 Oct 21 '21 at 10:01
  • 2
    DUAL exists on any Oracle database. It is owned by SYS, but anyone can use it via public synonym. – Littlefoot Oct 21 '21 at 10:04
  • 1
    In some other RDBMS you can select without specifying a table. In Oracle this is not possible, hence the existence of DUAL which contains a single column "DUMMY" and populated with a single row valued 'X' when the database is installed. – Lord Peter Oct 21 '21 at 10:05
  • Yes, thanks I really help. Sorry for asking one more question. If I want to update this result, how can I achieve it? Many thanks – aukk123 Oct 22 '21 at 01:43
  • Sorry, I don't understand what you mean. How would you want to "update" this result? – Littlefoot Oct 22 '21 at 05:10
1

You can use:

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

or

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

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
1

You can create "tables" within your query in many different ways, several of which have been illustrated in other answers already.

For your request I like an XML solution as shown below:

create table table1 (col1 varchar2(1));
insert into table1(col1) values('a');
insert into table1(col1) values('b');
insert into table1(col1) values('c');
commit;

select t1.col1, xmlcast(column_value as number) as col2
from   table1 t1 cross join xmltable('1 to 3')
;

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