| Concate 2 table columns [message #628841] |
Sat, 29 November 2014 02:30  |
 |
ajay.r1982
Messages: 8
Registered: November 2014
|
Junior Member |
|
|
Hi Guys,
I have 2 tables T1 , T2
T1 T2
------- ------
Name No
----- -----
A 1
B 2
C 3
i want a query which will give result as
O/P
---------
A1
B2
C3
|
|
|
|
|
|
|
|
| Re: Concate 2 table columns [message #628844 is a reply to message #628843] |
Sat, 29 November 2014 03:09  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You have not read the links I asked you to look at, have you? Please do so before you post again.
However, there is no solution to your problem unless you can define a rule to use when joining the rows. Why should A be joined to 1, but not to 2 or 3?
[Updated on: Sat, 29 November 2014 03:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Concate 2 table columns [message #628860 is a reply to message #628845] |
Sat, 29 November 2014 07:23 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ajay.r1982 wrote on Sat, 29 November 2014 10:39Here the question is not why i have to join A and 1. Can you do it or not. If yes then how.
I have read your links nothing seems to help.
The forum guide asks you to post a test case: the two CREATE TABLE statements and the six INSERT statements. Without this, it is not possible for anyone to write any SQL. And enclose the code within [code] tags.
However, as I said, if you cannot explain why one row should be joined to another row, there cannot be a solution.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Concate 2 table columns [message #628869 is a reply to message #628867] |
Sat, 29 November 2014 07:50 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Is that what you want?
orclz>
orclz> select e.empno || d.dname as empidanddname from emp e , dept d;
EMPIDANDDNAME
------------------------------------------------------
7369ACCOUNTING
7499ACCOUNTING
7521ACCOUNTING
7566ACCOUNTING
7654ACCOUNTING
7698ACCOUNTING
7782ACCOUNTING
7788ACCOUNTING
7839ACCOUNTING
7844ACCOUNTING
7876ACCOUNTING
7900ACCOUNTING
7902ACCOUNTING
7934ACCOUNTING
7369RESEARCH
7499RESEARCH
7521RESEARCH
7566RESEARCH
7654RESEARCH
7698RESEARCH
7782RESEARCH
7788RESEARCH
7839RESEARCH
7844RESEARCH
7876RESEARCH
7900RESEARCH
7902RESEARCH
7934RESEARCH
7369SALES
7499SALES
7521SALES
7566SALES
7654SALES
7698SALES
7782SALES
7788SALES
7839SALES
7844SALES
7876SALES
7900SALES
7902SALES
7934SALES
7369OPERATIONS
7499OPERATIONS
7521OPERATIONS
7566OPERATIONS
7654OPERATIONS
7698OPERATIONS
7782OPERATIONS
7788OPERATIONS
7839OPERATIONS
7844OPERATIONS
7876OPERATIONS
7900OPERATIONS
7902OPERATIONS
7934OPERATIONS
56 rows selected.
orclz> |
|
|
|
| Re: Concate 2 table columns [message #628870 is a reply to message #628867] |
Sat, 29 November 2014 08:35 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
avinashreddy wrote on Sat, 29 November 2014 08:48
you can do like this ... below following is example
Yes, you can do it with emp & dept but obviously not like you suggested. We can do it because we know relationship between emp and dept:
select e.empno || d.dname as empidanddname from emp e,dept d where d.depto = e.deptno
However we have no clue how OP's tables are related for the task. We can only guess, based on the sample, rows in each table should be ordered by the column and Nth row in T1 should be joined with Nth row in T2. But even then what should we do if T1 and T2 have different number of rows. Assuming we want to display 'NULL' for missing rows:
SQL> select *
2 from t1
3 /
N
-
C
B
X
SQL> select *
2 from t2
3 /
NO
----------
99
5
21
17
47
SQL> with a as (
2 select name,
3 row_number() over(order by name) rn
4 from t1
5 ),
6 b as (
7 select no,
8 row_number() over(order by no) rn
9 from t2
10 )
11 select nvl(name,'NULL') || nvl(to_char(no),'NULL') "O/P"
12 from a
13 full join
14 b
15 on b.rn = a.rn
16 /
O/P
--------------------------------------------
B5
C17
X21
NULL47
NULL99
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Concate 2 table columns [message #628908 is a reply to message #628901] |
Sun, 30 November 2014 12:59 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The proof (fixing your syntax error):
SQL> select * from t1 order by 1;
V
-
A
B
C
3 rows selected.
SQL> select * from t2 order by 1;
V2
----------
1
2
3
3 rows selected.
SQL> select table1.v1||table2.v2 from (select rownum as row1,v1 from t1) table1,(select rownum as row2, v2 from t2) table2
2 where table1.rpw1=table2.row2
3 /
where table1.rpw1=table2.row2
*
ERROR at line 2:
ORA-00904: "TABLE1"."RPW1": invalid identifier
SQL> select table1.v1||table2.v2 from (select rownum as row1,v1 from t1) table1,(select rownum as row2, v2 from t2) table2
2 where table1.row1=table2.row2
3 /
TABLE1.V1||TABLE2.V2
-----------------------------------------
B3
A2
C1
3 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
