0

I would liek to know how to concatnate several records from the same column from two different table in one. for instance below are my tables:

NOTE TABLE:

INVOICE NOTES
1000    REPLACE PUMP
1000    REPLACE OIL
1000    REPLACE FILTER
1111    WO# 123
1111    REPLACE GASKET
1234    REPLACE OIL

INVOICE TABLE:

INVOICE AMOUNT
1000    100
1111    50
1234    20

I can run this query and the result:

SELECT INV.INVOICE, INV.AMOUNT FROM INVOICE INV
INNER JOIN NOTES ON INV.INVOICE = NOTES.INVOICE

but I would like to combine the Notes for each invoice in one column in my result set. For examples my result should look like this

INVOICE  AMOUNT     NOTES
1000     100        REPLACE PUMP, REPLACE OIL, REPLACE FILTER
1111     50         WO# 123,REPLACE GASKET
1234     20         REPLACE OIL
jax
  • 840
  • 2
  • 17
  • 35
  • 2
    Which version of Oracle? Look at `listagg()` on 11g+. As long as the concatenated notes don't exceed the limits for your version. [Also see this article](https://oracle-base.com/articles/misc/string-aggregation-techniques). – Alex Poole Jul 07 '17 at 17:58
  • it's 12.1. I will try listagg(). – jax Jul 07 '17 at 18:07
  • Possible duplicate of [SQL Query to concatenate column values from multiple rows in Oracle](https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) – JonathanDavidArndt Jul 07 '17 at 18:43

1 Answers1

1

documentation - listagg

query

with inv_notes as
(select 1000 as INVOICE, 'REPLACE PUMP' as notes from dual union all
select 1000, 'REPLACE PUMP' from dual union all
select 1000, 'REPLACE OIL' from dual union all
select 1000, 'REPLACE FILTER' from dual union all
select 1111, 'WO# 123' from dual union all
select 1111, 'REPLACE GASKET' from dual union all
select 1234, 'REPLACE OIL' from dual
) 
,
inv_amount as (
select 1000 as INVOICE, 100 AMOUNT from dual union all
select 1111, 50 from dual union all
select 1234, 20 from dual)
select a.invoice, a.amount, listagg(n.notes, ',') WITHIN GROUP (ORDER BY a.invoice, a.amount) notes
from inv_notes n
 inner join inv_amount a on n.invoice = a.invoice
group by a.invoice, a.amount

result

1   1000    100 REPLACE FILTER,REPLACE OIL,REPLACE PUMP,REPLACE PUMP
2   1111    50  REPLACE GASKET,WO# 123
3   1234    20  REPLACE OIL
are
  • 2,535
  • 2
  • 22
  • 27
  • Thanks. but what if I have many records with many notes. Do I have to select each record with their notes? – jax Jul 07 '17 at 18:10
  • `with` is only for the sample – are Jul 07 '17 at 18:15
  • 1
    try this in on your database: `SELECT INV.INVOICE, INV.AMOUNT, listagg(NOTES.notes, ',') WITHIN GROUP (ORDER BY INV.INVOICE, INV.AMOUNT) notes FROM INVOICE INV INNER JOIN NOTES ON INV.INVOICE = NOTES.INVOICE group by INV.INVOICE, INV.AMOUNT` – are Jul 07 '17 at 18:16