Home » SQL & PL/SQL » SQL & PL/SQL » Merging of Rows with a sequence number
Merging of Rows with a sequence number [message #394964] Mon, 30 March 2009 22:57 Go to next message
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 #394976 is a reply to message #394964] Mon, 30 March 2009 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The solution can be achieved using PL/SQL.
Re: Merging of Rows with a sequence number [message #394980 is a reply to message #394976] Mon, 30 March 2009 23:31 Go to previous messageGo to next message
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 #394982 is a reply to message #394964] Mon, 30 March 2009 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Its showing PL/sql numeric or value error.
Error? What error? I do not see any error.
Actually I don't see any code.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Merging of Rows with a sequence number [message #395011 is a reply to message #394964] Tue, 31 March 2009 01:11 Go to previous messageGo to next message
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 #395020 is a reply to message #395011] Tue, 31 March 2009 01:53 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Please don't fake code.

This is NOT your actual code.
Re: Merging of Rows with a sequence number [message #395076 is a reply to message #394964] Tue, 31 March 2009 04:33 Go to previous message
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
Previous Topic: Mail with partially text and partially html
Next Topic: Dynamically prepare the query
Goto Forum:
  


Current Time: Tue Feb 18 01:43:02 CST 2025