Concatenating a column over multiple rows in Oracle 12c

Question

I have a table of notes related to orders from an old terminal system in Oracle 12c. Each order reference has several lines of notes, ordered by a sequence number.

I want to concatenate all of the relevant notes together for each order reference so that I can try to pull some address data out of it. The address data could be spread over several different sequence numbers. The structure is:

| SEQ | NOTE_TEXT                | ORDER | ... |
|-----|--------------------------|-------|-----|
| 1   | The address for this     |       |     |
| 2   | is 123 The Street, City, |       |     |
| 3   | County, Postcode         |       |     |
| 1   | This customer has ordered|       |     |
| 2   | this product on date     |       |     |
| 1   | Some other note          |       |     |
| 1   | This order is for A Smith|       |     |
| 2   | The address is 4 The Lane|       |     |
| 3   | City, County, Postcode   |       |     |
------------------------------------------------

What I would like to turn this into is:

|--------|---------------------------------------------------------------------------|
| ORDER  | NOTE_TEXT                                                                 |
|--------|---------------------------------------------------------------------------|
| ABC123 | The address for this is 123 The Street, City, County, Postcode            |
| DEF456 | This customer has ordered this product on date                            |
| GHI789 | Some other note                                                           |
| JKL012 | This order is for A Smith The address is 4 A Lane, City, County, Postcode |
|--------|---------------------------------------------------------------------------|

It would probably be good to trim each note row before concatenating but I also need to make sure that I put a space between the join of two rows, just in case someone has filled the full line with text. Oh and the sequences are out of order so I need to order by first too.

Thanks for your help!

Gurwinder Singh · Accepted Answer · 2017-09-15 16:32:29Z

2

You can use listagg for this:

select "order" || listagg(seq, '') within group (order by seq) as "order",
    listagg(trim(note_text), ' ') within group (order by seq) as note_text
from your_table
group by "order";

Also, note that order is a reserved keyword in oracle. Best use some other identifier or use " to escape it.

edited Sep 15, 2017 at 16:32

answered Sep 15, 2017 at 14:05

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5683823 Over a year ago

The OP didn't show values in the order column in the simulated inputs, but it is likely they are similar to ABC123 already; 1, 2, 3 are not the seq numbers. So, the first column in SELECT can just be "order". (Glad you pointed out the misuse of a reserved Oracle word, too!)

Nikkunaku Over a year ago

Thanks GurV! Yes, the simulated inputs at the top were supposed to have actual orders numbers in there not blanks :P Luckily the person who put the table together used a far more cryptic column name than just 'order' and it was me trying to simplify things - badly lol.

Collectives™ on Stack Overflow

Concatenating a column over multiple rows in Oracle 12c

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related