Sponsored Links
Sponsored Link

sponsored links

Collapse

Announcement

Collapse
No announcement yet.

SQL adding lines of text from a column to 1 long field

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL adding lines of text from a column to 1 long field

    Hello all, I am not a programmer but have been asked to write a report from a file that sorts text lines by sequence number and provide a report that strings all those lines into one single line. The line sequence can go up to 999 so I need to make the text field VERY long.
    Thanks

    Example

    Acct number date Line Seq Text
    123456 20180103 1 Call from client regarding
    123456 20180103 2 Invoice # 123. Responded
    123456 20180103 3 that invoice had been sent.

    I need it to look like
    Acct number date Line Seq Text
    123456 20180103 1 Call from client regarding Invoice # 123. Responded that invoice had been sent.

  • #2
    If you are on Release 7.2 or higher, you may use the LISTAGG aggregate function.
    Code:
    Select  Acct, NumDate, ListAgg(Text, ' ')
      from mytable
      group By Acct, NumDate;
    To make sure that your textes are concatenated in the expected sequence (seq asc), you may add an order by for the LISTAGG Function:
    Code:
    Select  Acct, NumDate, ListAgg(Text, ' ') within Group (Order By Seq)
      from mytable
      group By Acct, NumDate;
    If you are still on Release 7.1 you may use some kind of recursion:
    Code:
    Select Acct, NumDate, Max(Sys_Connect_By_Path(Text, ' '))
      from MyTable
      start with seq = 1 connect by prior seq = seq - 1
      Group By Acct, NumDate;
    Birgitta

    Comment


    • #3
      That is good stuff Birgitta!

      I'm going to need that functionality for an upcoming project.

      Thank you!

      Walt

      Comment

      sponsored links

      Collapse

      Working...
      X