1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

wm_concat with check on length

Discussion in 'SQL PL/SQL' started by DomCotton, Oct 9, 2013.

  1. DomCotton

    DomCotton Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am trying to write a query, my details are as below:

    Table: SerialNumbers, single values with 1-n rows. Example
    14564864685
    48434564641
    14515
    16987561
    48949948648

    I currently have the following query:

    SELECT wm_concat(serial_no)
    from SerialNumbers
    group by rownum - mod(rownum-1,5)

    This currently returns the following:

    14564864685,48434564641,14515,16987561,48949948648

    Because the length of my serial no could be anything from 4 digits to maybe 30, I want to check the length of the resulting string from the wm_concat function (with the "group by" 5) and if its longer than a certain number of characters then split a line. Is this possible? So I want it to look something like below:

    14564864685,48434564641,
    14515,16987561,
    48949948648

    I am unable to think of a solution.

    I cannot use PL/SQL here it has to be SQL...

    Hoping somebody can come up with something!

    Kind regards,
    Dom

    p.s. if this is in the wrong forum I apologise!
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  3. DomCotton

    DomCotton Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    OK so after writing this (very quickly!) I looked at it again, and I think I have found a solution:

    with data as (
    SELECT serial_no, sum(length(serial_no)+1) over (order by serial_no) as LenSum
    from SerialNumbers
    )
    select wm_concat(serial_no) from data
    group by floor(LenSum/XX)+1

    and I put whatever I want the maximum number of characters to be the XX.

    So far I can't make it break so could be good!!!

    Hoping this helps somebody because I couldn't find a solution by searching~!

    Dom
     
  4. DomCotton

    DomCotton Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Ah yes sorry forgot about the version! I really appreciate the speedy response, looking at that post I may want to look at listagg and not wm_concat. As it goes, I am on Oracle 11.

    Thanks
    Dom