Solved

Oracle 10g - Concatonate Multiple Rows - PART 2

Posted on 2013-05-17
5
555 Views
Last Modified: 2013-05-18
A slight revision to my first request.

I originally asked  to concatonate mutliple rows of text together in a select query. The answer provided was

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

Open in new window


I have now realized that there are no CR/LF characters at the end of some lines and the resulting output is mashed together.

Can we ..... look at each line, if 44 or less characters in length, append a CR/LF before concatonating the lines?
0
Comment
Question by:JDCam
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39175806
>>Can we ..... look at each line, if 44 or less characters in length, append a CR/LF before concatonating the lines?

Yes, if this is what you want:
EXTRACT(XMLAGG(XMLELEMENT("s", case when length(line_text)<44 then line_text || chr(10) else line_text end) order by line_num asc)

Are you sure this is what you want?  It doesn't seem valid to me.

If you use the code I posted in the other question to add a space character, you could always remove duplicate spaces with a simple regex:

regexp_replace(your_final_output,'[ ]+',' ')
0
 

Author Closing Comment

by:JDCam
ID: 39175962
Yes, its pretty good!

A little confusing to explain, but the application allows input at 70 char per line, then stores it in a column that is only 45 char (no idea!). Characters beyond 45 are inserted to a new row. If full lines of text were entered, the result is alternating rows of 45 and 15.  this combined with the fact they may have actually typed short lines and used CR/LF makes in near impossible to re-construct with accuarcy.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176471
may I ask, are there any other fields in this comment table (e.g. a datetime)?

I'm just wondering if there's a way to use of lead/lag which might produce a better result, this is not what you are looking for, but it might give rise to ideas
select
  ord_num
, line_num
, LAG (line_comment) OVER (partition by ord_num order by line_num) lag_1
, line_comment
, LEAD (line_comment) OVER (partition by ord_num order by line_num) lead_1
from ord_comment

Open in new window

http://sqlfiddle.com/#!4/072bc/1
Maybe if used on a larger sample of your data  (with CR/LFs) it might reveal something(?)
(curious method of comment storage v.odd)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176609
this might be helpful? it doesn't attempt a concatenation, but it may lead to placing the CR's appropiately (? maybe)
select
  ord_num
, case
      when len_lead = 45 and len_lag = 0   then line_comment
      when len_lag  = 45                   then lag1 || line_comment
      when len_this < 45 and len_lead < 45 then line_comment
  end as concat_comment
from (
      select
        ord_num
      , line_num
      , LAG (line_comment) OVER (partition by ord_num order by line_num) lag1
      , line_comment
      , LEAD (line_comment) OVER (partition by ord_num order by line_num) lead1
      , NVL(length(LAG (line_comment) OVER (partition by ord_num order by line_num)),0) len_lag
      , length(line_comment) len_this
      , NVL(length(LEAD (line_comment) OVER (partition by ord_num order by line_num)),0) len_lead
      from ord_comment
     )    
where case
        when len_lead = 45 and len_lag = 0 then 1
        when len_lag  = 45 then 1
        when len_this < 45 and len_lead < 45 then 1
      end = 1

Open in new window

& I'm not going to claim it's highly efficient either. http://sqlfiddle.com/#!4/3a33c/2
0
 
LVL 32

Expert Comment

by:awking00
ID: 39177288
>>I have now realized that there are no CR/LF characters at the end of some lines and the resulting output is mashed together. <<

Does that mean there could be some lines less than 44 characters that do contain CR/LF characters?
If so, the accepted solution would result in double linefeeds, but could be simply modified to avoid such a scenario -
EXTRACT(XMLAGG(XMLELEMENT("s", case when length(line_text)<44 and instr(line_text,chr(10)) = 0 then line_text || chr(10) else line_text end) order by line_num asc)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question