CODEWITHSUNDEEP
sqloracle-database
Savke
Savke

Reputation: 131

How to union multiple times same SQL

I have query like this

select 1,2,3 from dual
union all
select 1,2,3 from dual

When I need to add new row, i put another union all, and that's ok. But problem appear when I need several union, for example 20. It is really annoying and not efficient to make another 17 unions. Is there a way (some procedure, function whatever) to make it faster and more elegant?

Upvotes: 1

Views: 639

Answers (3)

William Robertson
William Robertson

Reputation: 15991

A variation on Littlefoot's answer:

select 1, 2, 3
from   xmltable('1 to 20');

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Sometimes it's easier to use json_table in such cases:

select *
from json_table(
  '{data:[
     [1,2,3,"abc"],
     [2,3,4,"def"],
     [3,4,5,"xyz"],
    ]
   }'
   ,'$.data[*]'
   columns
      a number path '$[0]',
      b number path '$[1]',
      c number path '$[2]',
      d varchar2(30) path '$[3]'
);

Results:

         A          B          C D
---------- ---------- ---------- ------------------------------
         1          2          3 abc
         2          3          4 def
         3          4          5 xyz

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

No problem, easy-peasy.

SQL> select 1, 2, 3
  2  from dual
  3  connect by level <= 10;

         1          2          3
---------- ---------- ----------
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3

10 rows selected.

SQL>

Upvotes: 4

Related Questions