Merging of Rows with a sequence number [message #394964] |
Mon, 30 March 2009 22:57  |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
Is there any best way so that I can achieve the following.
My Query is
Select text from table_A where col2 = 123 order by col1;
This query will return more than 1 number of rows.
Quote: |
AAA
BBB
CCC
XXXX
YYY
ZZZ
VVV
OOO
UUU
QQQ
|
Now I want the desired output as :
Quote: |
1. AAA 2. BBB 3. CCC 4. XXX
X 5. YYY 6. ZZZ 7. VVV 8. O
OO 9. UUU 10. QQQ
|
The length of the line should not exceed more than 72 characters. If it execeeds then break the line and write it in the next line. Please refer the above output and help me in getting this output.
|
|
|
|
Re: Merging of Rows with a sequence number [message #394980 is a reply to message #394976] |
Mon, 30 March 2009 23:31   |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
I didnt get the full logic for achieving thro' PL/SQL. Because when I try to get all the values in a variables..I could not ..Its showing PL/sql numeric or value error. Because the "text" column contains VARCHAR2(250) bytes. So each row contains 250 bytes...I need to mingle it altogether ..
|
|
|
|
Re: Merging of Rows with a sequence number [message #395011 is a reply to message #394964] |
Tue, 31 March 2009 01:11   |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
declare
cursor c1 is Select text from table_A where col2 = 123 order by col1;
l_txt_72 varchar2(72);
rec_txt varchar2(4000);
begin
end;
for rec in c1
loop
rec_txt := rec.text;
i := i+1;
LOOP
l_txt_72 := SUBSTR(rec_txt,l_i,72);
IF l_txt_72 = ' ' OR l_txt_72 IS NULL THEN
EXIT;
END IF;
UTL_FILE.PUT_LINE(file_HandleOut, || i || '. '|| l_txt_72);
l_i := l_i + 72;
END LOOP;
In this i am getting pl/sql numeric error.
|
|
|
|
Re: Merging of Rows with a sequence number [message #395076 is a reply to message #394964] |
Tue, 31 March 2009 04:33  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Start with the following:
SQL> col res format a27
SQL> with
2 data as (
3 select lpad(chr(ascii('A')+level-1),
4 trunc(dbms_random.value(2,5)),
5 chr(ascii('A')+level-1)) val
6 from dual
7 connect by level <= 10
8 ),
9 numbered as (
10 select row_number() over (order by val) rn,
11 count(*) over () cnt,
12 val
13 from data
14 )
15 select replace(substr(sys_connect_by_path(rn||'. '||val,'/'),2),'/',' ') res
16 from numbered
17 where rn = cnt
18 connect by prior rn = rn-1
19 start with rn = 1
20 /
RES
---------------------------
1. AA 2. BBB 3. CC 4. DDDD
5. EEEE 6. FF 7. GG 8. HHHH
9. II 10. JJ
1 row selected.
Regards
Michel
|
|
|