Solved

Oracle 10g - Concatonate Multiple Rows - PART 2

Posted on 2013-05-17
5
558 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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 49

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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

717 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