• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

Oracle 10g - Concatonate multiple rows

Experts,

A table contains text comments for an order. During input, the application breaks the text at  60 char and inserts as multiple rows. Each row is given a sequential line number.

I need to output this data in a report, but doing so as-is looks very broken and choppy.  I want to use a query to concatenate the rows into one and pass that into the report designer where it can be properly formatted

A simple Select looks like this:
Select
Line_num,
Line_text 
from Ord_Comment 
where CompCode = ‘A1’
and Ord_num = 628649

Open in new window

Output looks like
1	SO
2	CS Hold 5 Items are being ordered in less tha
3	n Full
4	Master Case Qty. Emailed customer to revise P
5	O. JO 4-23

Open in new window


Desired output is a single column, single row with all Line_Text concatenated. Note:  there are CR/LF after row 1 and 3, the character needs to remain in the concatenated string

Can this be done in a standard query without creating a user defined function?
0
JDCam
Asked:
JDCam
  • 4
  • 3
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Try this:

drop table tab1 purge;
create table tab1(compCode char(2), ord_num number, line_num number, line_text varchar2(50));

--add a CHR(10) at the end of line 1 and 3
insert into tab1 values('A1',628649,1,'SO' || chr(10));
insert into tab1 values('A1',628649,2,'CS Hold 5 Items are being ordered in less tha');
insert into tab1 values('A1',628649,3,'n Full' || chr(10));
insert into tab1 values('A1',628649,4,'Master Case Qty. Emailed customer to revise P');
insert into tab1 values('A1',628649,5,'O. JO 4-23');
--dummy row justs for grins
insert into tab1 values('ZZ',12345,1,'Hello World');
commit;


Select
Line_num,
Line_text 
from tab1
where CompCode = 'A1'
and Ord_num = 628649
/


select 
 TRIM(
           EXTRACT(XMLAGG(XMLELEMENT("s", line_text || ' ') order by line_num asc), '/s/text()').getclobval()
       )
from tab1
where CompCode = 'A1'
   and Ord_num = 628649
group by compCode,ord_num;

Open in new window

0
 
awking00Commented:
slightwv,
I think concatenating the space will produce results like -
SQL> select
  2   TRIM(
  3             EXTRACT(XMLAGG(XMLELEMENT("s", line_text||' ') order by line_num
 asc), '/s/text()').getclobval()
  4         )
  5  from tab1
  6  where CompCode = 'A1'
  7     and Ord_num = 628649
  8  group by compCode,ord_num;

TRIM(EXTRACT(XMLAGG(XMLELEMENT("S",LINE_TEXT||'')ORDERBYLINE_NUMASC),'/S/TEXT()'
--------------------------------------------------------------------------------
SO
 CS Hold 5 Items are being ordered in less tha n Full
 Master Case Qty. Emailed customer to revise P O. JO 4-23
Where there is a space between the 'tha' and 'n' plus a space after each line feed.

Removing the space concatenation(which also removes the need for the trim function) would produce results like this -
SQL> select
  2             EXTRACT(XMLAGG(XMLELEMENT("s", line_text) order by line_num asc)
, '/s/text()').getclobval()
  3  from tab1
  4  where CompCode = 'A1'
  5     and Ord_num = 628649
  6  group by compCode,ord_num;

EXTRACT(XMLAGG(XMLELEMENT("S",LINE_TEXT)ORDERBYLINE_NUMASC),'/S/TEXT()').GETCLOB
--------------------------------------------------------------------------------
SO
CS Hold 5 Items are being ordered in less than Full
Master Case Qty. Emailed customer to revise PO. JO 4-23

which may be more desirable.
0
 
slightwv (䄆 Netminder) Commented:
>>which may be more desirable.

Good point!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JDCamAuthor Commented:
Excellent guys.  I will share the points evenly.

I want to make a slight shange to this request. I will repost as a new question in a few moments.  Thanks
0
 
awking00Commented:
I think slightwv should get the lion's share of the points since it was really his solution and I just pointed out a small anomaly.
0
 
slightwv (䄆 Netminder) Commented:
It's OK awking00,

The flaw you found was just as important and to be honest, that core code was borrowed from sdstuber!
0
 
awking00Commented:
Maybe we can bring Sean in on the part 2 question so he can get his due :-)
0
 
slightwv (䄆 Netminder) Commented:
Let him fend for himself!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now