How to combine rows together where only one column is different between rows

Question

I have a query which result in expense claims.

Sometimes the receipt attached is attached to the header level rather than the line level so the output is that the rows runs twice per expense line as it cannot map the exact receipt attachment.

How do I combine the 'receipt name' so it can aggregate in one row. I need it so I can look up the exact receipts matched to the expense line.

Please see table below.

Claimant  Claim_Date  Claim_Number  Description  Amount  Receipt_Name
Name A    01/08/20    10001         Coffee       £4.00   Coffee at Star.jpg
Name B    02/08/20    10002         Stationery   £20.00  Stationery Receipt.jpg
Name B    02/08/20    10002         Stationery   £20.00  Laptop.jpg
Name B    02/08/20    10002         Laptop Buy   £600.00 Stationery Receipt.jpg
Name B    02/08/20    10002         Laptop Buy   £600.00 Laptop.jpg
Name C    02/08/20    10003         Biscuits     £10.00  Mcv's.jpg
Name A    02/08/20    10004         Office Wear  £100.00 Suitsonline.jpg

I would like it to be

   Claimant  Claim_Date  Claim_Number  Description  Amount  Receipt_Name
    Name A    01/08/20    10001         Coffee       £4.00   Coffee at Star.jpg
    Name B    02/08/20    10002         Stationery   £20.00  Stationery Receipt.jpg, Laptop.jpg
    Name B    02/08/20    10002         Laptop Buy   £600.00 Stationery Receipt.jpg, Laptop.jpg
    Name C    02/08/20    10003         Biscuits     £10.00  Mcv's.jpg
    Name A    02/08/20    10004         Office Wear  £100.00 Suitsonline.jpg

I have tried to use LISTAGG and have tried certain group by functions.

Here is my current Query (cut down)

select  claimant.display_name claimant, 
    expr.report_submit_date claim_date,
    expr.expense_report_num claim_number,
    
    exp.description description,
    
    round(exp.receipt_amount * exp.exchange_rate,2) amount,
    
    case when linefndtl.file_name IS NULL then fndtl.file_name
    when fndtl.file_name IS NULL then linefndtl.file_name
    when fndtl.file_name IS NULL AND linefndtl.file_name IS NULL then 'Missing'
    ELSE NULL
    END Expense_File_Name,
        
-- Main Sources
from exm_expense_reports expr  
left join exm_expenses exp on exp.expense_report_id = expr.expense_report_id
left join exm_expense_dists expdist on expdist.expense_report_id = exp.expense_report_id 
                                    and expdist.expense_id = exp.expense_id

-- Claimant
left join per_person_names_f_v claimant on claimant.person_id = expr.person_id 
                                        and trunc(sysdate) between claimant.effective_start_date and  claimant.effective_end_date

-- Attachment at Header
left join exm_expenses headerexmexpenses on headerexmexpenses.expense_id = exp.itemization_parent_expense_id
left join fnd_attached_documents fndad on to_char(headerexmexpenses.expense_id) = fndad.pk1_value
left join fnd_documents_tl fndtl on fndtl.document_id = fndad.document_id
-- Attachment at Line
left join fnd_attached_documents linefndad on to_char(exp.expense_id) = linefndad.pk1_value
left join fnd_documents_tl linefndtl on linefndad.document_id = linefndtl.document_id
where 1 = 1
    and nvl(exp.itemization_parent_expense_id, 1) <> -1

All results are the same except the file name row.

Many thanks!

which DBMS you are using? If SQL Server then pls try STRING_AGG insted of LISTAGG, Thank you — gm arif, Aug 07 '20 at 21:58

score 1 · Accepted Answer · answered Aug 07 '20 at 21:35

I would think that LISTAGG does the job.

TEST CTE represents your current data. Lines #11 onward show that it works.

SQL> with test (Claimant, Claim_Date, Claim_Number, Description, Amount, Receipt_Name) as
  2  (select
  3  'Name A',    '01/08/20',    10001,         'Coffee'       ,4   ,'Coffee at Star.jpg' from dual union all select
  4  'Name B',    '02/08/20',    10002,         'Stationery'   ,20  ,'Stationery Receipt.jpg' from dual union all select
  5  'Name B',    '02/08/20',    10002,         'Stationery'   ,20  ,'Laptop.jpg' from dual union all select
  6  'Name B',    '02/08/20',    10002,         'Laptop Buy'   ,600 ,'Stationery Receipt.jpg' from dual union all select
  7  'Name B',    '02/08/20',    10002,         'Laptop Buy'   ,600 ,'Laptop.jpg' from dual union all select
  8  'Name C',    '02/08/20',    10003,         'Biscuits'     ,10  ,'Mcv''s.jpg' from dual union all select
  9  'Name A',    '02/08/20',    10004,         'Office Wear'  ,100 ,'Suitsonline.jpg' from dual
 10  )
 11  select claimant, claim_date, claim_number,
 12    description, amount,
 13    listagg(receipt_name, ', ') within group (order by receipt_name) receipt_name
 14  from test
 15  group by Claimant, Claim_Date, Claim_Number, Description, Amount
 16  order by claimant;

CLAIMA CLAIM_DA CLAIM_NUMBER DESCRIPTION     AMOUNT RECEIPT_NAME
------ -------- ------------ ----------- ---------- ----------------------------------------
Name A 01/08/20        10001 Coffee               4 Coffee at Star.jpg
Name A 02/08/20        10004 Office Wear        100 Suitsonline.jpg
Name B 02/08/20        10002 Laptop Buy         600 Laptop.jpg, Stationery Receipt.jpg
Name B 02/08/20        10002 Stationery          20 Laptop.jpg, Stationery Receipt.jpg
Name C 02/08/20        10003 Biscuits            10 Mcv's.jpg

SQL>

The simplest way to do it is to use your current query as a CTE and apply the rest of the query (lines #11 onward, right?) to it.

thanks I had to add brackets and it worked (listagg(receipt_name, ', ') within group (order by receipt_name)) — J1212, Aug 07 '20 at 22:04

How to combine rows together where only one column is different between rows

1 Answers1