Ok, there's a few ways to do this.
If you have 9i or later you can use sys_connect_by_path.
Now, you need some sort of key or flag to make sure
You concat in order. I'm going to add a fake primary
Key your sample data:
SQL> select *
2 from (
3 select '00001' No, '001' Code from dual union
4 select '00002' No, '003' Code from dual union
5 select '00003' No, '004' Code from dual
6 ) table1,
7 (
8 select '00001' No, 'This' Remark, 1 pk from dual union
9 select '00001' No, 'is' Remark, 2 pk from dual union
10 select '00001' No, 'Sample' Remark, 3 pk from dual union
11 select '00002' No, 'I' Remark, 4 pk from dual union
12 select '00002' No, 'Think' Remark, 5 pk from dual union
13 select '00002' No, 'it is' Remark, 6 pk from dual union
14 select '00002' No, 'Difficult' Remark, 7 pk from dual
15 ) table2
16 where table1.no=table2.no;
NO COD NO REMARK PK
----- --- ----- --------- ----------
00001 001 00001 Sample 3
00001 001 00001 This 1
00001 001 00001 is 2
00002 003 00002 Difficult 7
00002 003 00002 I 4
00002 003 00002 Think 5
00002 003 00002 it is 6
7 rows selected.
Now, just use ROW_NUMBER OVER to assign a ranking to use with CONNECT BY
(using views for readability):
SQL> ed
Wrote file afiedt.buf
1 select code,
2 no,
3 replace(sys_connect_by_path(remark,'#'),'#',' ') remark
4 from (
5 select table1.code,
6 table2.no,
7 table2.remark,
8 count(*)over(partition by table2.no) cnt,
9 row_number()over(partition by table2.no
10 order by table2.pk) rn
11 from table1,
12 table2
13 where table1.no=table2.no
14 )
15 where level = cnt
16 start with rn = 1
17* connect by prior rn = rn-1 and prior no = no
SQL> /
COD NO REMARK
--- ----- -------------------------------------------------------
001 00001 This is Sample
003 00002 I Think it is Difficult
good luck,
Anthony
-----Original Message-----
From: Dhaval Gandhi
[mailto:oracledba-ezmlmshield-x32592380.[Email address protected]
Sent: Thursday, January 05, 2006 6:06 AM
To: LazyDBA Discussion
Subject: How to fetch record ?
I have two tables like :
Table 1
No Code
00001 001
00002 003
00003 004
Table 2
No Remark
00001 This
00001 is
00001 Sample
00002 I
00002 Think
00002 it is
00002 Difficult
I want to write a query which gives me following output.
No Remark
00001 This is Sample
00002 I Think it is Difficult
How can I generate output like this ?
Regards,
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page