Home » SQL & PL/SQL » SQL & PL/SQL » How do I combine data from multiple rows into a single row? (Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production, Solaris 10)
How do I combine data from multiple rows into a single row? [message #384680] Wed, 04 February 2009 16:19 Go to next message
mhegemeyer
Messages: 2
Registered: February 2009
Location: Houston
Junior Member
I have a report that needs to be cleaned up and there are several lines of text that could be wrapped up into a single result value, but I can't seem to figure out how to accomplish this in my query. I've used some of the examples provided by Art Metzer in message #35139 and stragg function got me as far as having the single row result. However I have not been able to accomodate the sorting requirements to ensure the lines are assembled in proper sequence. My requirements are demonstrated below:

I have the following data in my table:


ORD SEQ TEXT
----- --- ----------------------------
12345 003 smart when it comes to SQL.

12345 002 Mark and I am not very

12345 004 I have been able to wing it

12345 006 has me stumped. I would

12345 005 until now but this problem

12345 001 Hello, my name is

12345 010 lines of text in order.

12345 009 that I could get to put

12345 008 greatly appreciate any help

54321 003 the fence.

54321 001 The fox ran fast

54321 002 and jumped under



******Start of Script********

create table Z$_Temp_Text(Ord varchar2(5),Seq varchar2(3),Text varchar2(80));
insert into Z$_Temp_Text values (‘12345’,‘003’,‘smart when it comes to SQL.’);
insert into Z$_Temp_Text values (‘12345’,‘002’,‘Mark and I am not very’);
insert into Z$_Temp_Text values (‘12345’,‘004’,‘I have been able to wing it’);
insert into Z$_Temp_Text values (‘12345’,‘006’,‘has me stumped. I would’);
insert into Z$_Temp_Text values (‘12345’,‘005’,‘until now but this problem’);
insert into Z$_Temp_Text values (‘12345’,‘001’,‘Hello, my name is’);
insert into Z$_Temp_Text values (‘12345’,‘010’,‘lines of text in order.’);
insert into Z$_Temp_Text values (‘12345’,‘009’,‘that I could get to put’);
insert into Z$_Temp_Text values (‘12345’,‘008’,‘greatly appreciate any help’);
insert into Z$_Temp_Text values (‘54321’,‘003’,‘the fence. ’);
insert into Z$_Temp_Text values (‘54321’,‘001’,‘The fox ran fast’);
insert into Z$_Temp_Text values (‘54321’,‘002’,‘and jumped under’);

commit;

******End of Script********


With a SQL statement I need to get two resulting records as followed:


Ord Note
===== ======================================================

12345 Hello, my name is Mark and I am not very smart when it
comes to SQL. I have been able to wing it until now but
this problem has me stumped. I would greatly appreciate
any help that I could get to put lines of text in order.

54321 The fox ran fast and jumped under the fence.


I would appreciate any advice you have to offer.

Thanks,
Mark
icon7.gif  Re: How do I combine data from multiple rows into a single row? [message #384685 is a reply to message #384680] Wed, 04 February 2009 20:50 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Does this help?


select ord,TRIM(replace(replace(xmlagg(xmlelement("a",text) ORDER BY SEQ),'<a>',NULL),'</a>',' ')) 
from Z$_Temp_Text
group by ord



Output
-------

12345 Hello, my name is Mark and I am not very smart when it comes to SQL. I have been able to wing it until now but this problem has me stumped. I would greatly appreciate any help that I could get to put lines of text in order.
54321 The fox ran fast and jumped under the fence.

Regards,
Raajesh

[Updated on: Thu, 05 February 2009 00:45] by Moderator

Report message to a moderator

Re: How do I combine data from multiple rows into a single row? [message #384719 is a reply to message #384680] Wed, 04 February 2009 23:39 Go to previous messageGo to next message
st33chen
Messages: 11
Registered: January 2009
Junior Member
hi,

my solution is :
  with data as (
        select ord, seq, text,
               row_number() over (partition by ord order by seq) rn,
                   count(*) over (partition by ord) cnt
          from Z$_Temp_Text
        )
   select ord, trim(replace(sys_connect_by_path(text,'_'),'_',' ')) scbp
     from data
    where rn = cnt
    start with rn = 1
   connect by prior ord = ord
      and prior rn = rn-1
    order by ord;

where '_' is a character that is not in the data of field text.


[Edit MC: added code tags, please do it yourself next time, see OraFAQ Forum Guide]

[Updated on: Thu, 05 February 2009 00:47] by Moderator

Report message to a moderator

icon14.gif  Re: How do I combine data from multiple rows into a single row? [message #384870 is a reply to message #384685] Thu, 05 February 2009 09:02 Go to previous message
mhegemeyer
Messages: 2
Registered: February 2009
Location: Houston
Junior Member
I have tested this locally and it satisfies my needs exactly.

I was actually following another feed found elsewhere similar to this yesterday, however I was completely unable to resolve my syntax issues.

Thank you so much for the feedback.
Previous Topic: How to get Second Highest Salary in employee table
Next Topic: Deleteting child table records
Goto Forum:
  


Current Time: Sun Dec 11 04:00:34 CST 2016

Total time taken to generate the page: 0.07237 seconds