Equivalent of R cbind() function in Oracle?

Question

I am new to sql and struggling to solve this very simple task.

Considering,

    with table1 as (select '1' col1 from dual union
select '2' col1 from dual union
select 'NO_PATTERN' col1 from dual union
select 'RANDOM_STUFF' col1 from dual)
    select * from table1;

and,

    with table2 as (select 'aaa' col2 from dual union
select '4' col2 from dual union
select 'qwewqeq' col2  from dual
union select 'UUUUUU' col2 from dual)
    select * from table2;

I want to perform a cbind() between the two columns into a new table which is the "vertical union" of table1.[col1] and table2[col2].

The EXPECTED solution is:

    with solution as (select '1' col1, 'aaa' col2 from dual union
select '2' col1, '4' col2 from dual union
select 'NO_PATTERN'  col1, 'qwewqeq' col2 from dual union
select 'RANDOM_STUFF'  col1, 'UUUUUU'  col2 from dual)
    select * from solution;

Any idea?

Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Jun 06 '18 at 12:42
I posted my question with sample data (table1, table2) and expected output (solution). How did you format the code using SQL colour? — Seymour, Jun 06 '18 at 12:44
I can't get the point. For example why should col1=2 and col2=4 be on the same row? What is the way to "link" a record to another one? — Aleksej, Jun 06 '18 at 13:05
The idea is to perform the merge/join solely based on the position of the record in the table — Seymour, Jun 06 '18 at 13:09
@Aleksej - unlike Oracle tables, R data frames have a default order to them. I think Seymour wants them joined based on their default ("unordered") rownums in the sample data, even though this is unreliable and not a best practice in Oracle. — kfinity, Jun 06 '18 at 13:10
@kfinity Exactly this! thank you for translating my problem! — Seymour, Jun 06 '18 at 13:20

Aleksej · Accepted Answer · 2018-06-06T13:50:29.190

In Oracle you need to explicitly define some value to get ordered data; without that, you could have different results every time you run a query.

With your data, this:

WITH table1 AS
         (SELECT 1 rn, '1'            col1 FROM DUAL UNION
          SELECT 2 rn, '2'            col1 FROM DUAL UNION
          SELECT 3 rn, 'NO_PATTERN'   col1 FROM DUAL UNION
          SELECT 4 rn, 'RANDOM_STUFF' col1 FROM DUAL),
     table2 AS
         (SELECT 1 rn, 'aaa'     col2 FROM DUAL UNION
          SELECT 2 rn, '4'       col2 FROM DUAL UNION
          SELECT 3 rn, 'qwewqeq' col2 FROM DUAL UNION
          SELECT 4 rn, 'UUUUUU'  col2 FROM DUAL)
SELECT col1, col2, t1.rn
from (select  row_number() over (order by rn) as rn, col1 from table1 ) t1
       inner join 
     (select row_number() over (order by rn) as rn, col2 from table2 ) t2
     on (t1.rn = t2.rn)

gives:

COL1         COL2            RN
------------ ------- ----------
1            aaa              1
2            4                2
NO_PATTERN   qwewqeq          3
RANDOM_STUFF UUUUUU           4

Without an explicit ordering, for exampe, this

WITH table1 AS
         (SELECT '1'            col1 FROM DUAL UNION
          SELECT '2'            col1 FROM DUAL UNION
          SELECT 'NO_PATTERN'   col1 FROM DUAL UNION
          SELECT 'RANDOM_STUFF' col1 FROM DUAL),
     table2 AS
         (SELECT 'aaa'     col2 FROM DUAL UNION
          SELECT '4'       col2 FROM DUAL UNION
          SELECT 'qwewqeq' col2 FROM DUAL UNION
          SELECT 'UUUUUU'  col2 FROM DUAL)
SELECT col1, col2, t1.rn
from (select  rownum as rn, col1 from table1 ) t1
       inner join 
     (select rownum as rn, col2 from table2 ) t2
     on (t1.rn = t2.rn)

gives

COL1         COL2            RN
------------ ------- ----------
1            4                1
2            UUUUUU           2
NO_PATTERN   aaa              3
RANDOM_STUFF qwewqeq          4

This answer does not work as R cbind() function because it order the column. Instead I am looking for a solution that simply perform a vertical merge between two columns. Is it possible? — Seymour, Jun 06 '18 at 12:55
I dont't know cbind. Please try to post a wider set of sample data, to clarify your need — Aleksej, Jun 06 '18 at 12:56
I think just removing the analytic `over (order by col)` clauses would give the desired results. — kfinity, Jun 06 '18 at 13:13
That is the first thing I tried when @Aleksej replied, but it gives me an error — Seymour, Jun 06 '18 at 13:21
Oh, I forgot that the function works differently. I guess you'd have to replace `row_number()` with `rownum` to get the unsorted order. — kfinity, Jun 06 '18 at 13:26

score 0 · Answer 2 · edited Oct 23 '18 at 16:27

If you don't want to order them, this would be a simple way to do it -

select *, ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO into #0 FROM Table 2

select *, ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO into #1 FROM Table1

select a.*, b.* into #3 from #0 a join #1 b on a.SNO = b.SNO

Got the idea from here -

https://blog.sqlauthority.com/2015/05/05/sql-server-generating-row-number-without-ordering-any-columns/

Equivalent of R cbind() function in Oracle?

2 Answers2

Linked