Link to home
Create AccountLog in
Avatar of JDCam
JDCam

asked on

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?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>which may be more desirable.

Good point!
Avatar of JDCam

ASKER

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
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.
It's OK awking00,

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