时间戳

首页 > 解决方案 > 如何在行之间只有一列不同的情况下将行组合在一起

sql - 如何在行之间只有一列不同的情况下将行组合在一起

问题描述

我有一个导致费用索赔的查询。

有时附加的收据附加到抬头级别而不是行级别，因此输出是每个费用行运行两次，因为它无法映射确切的收据附件。

如何组合“收据名称”，以便它可以聚合在一行中。我需要它，以便查找与费用行匹配的确切收据。

请参阅下表。

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

我希望它是

   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

我尝试使用 LISTAGG 并尝试了某些按功能分组。

这是我当前的查询（减少）

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

除文件名行外，所有结果都相同。

非常感谢！

标签： sqloraclepsqlbi-publisher

解决方案

我认为这LISTAGG可以完成这项工作。

TESTCTE 代表您当前的数据。第 11 行以后显示它有效。

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>

最简单的方法是使用您当前的查询作为 CTE 并将查询的其余部分（第 11 行向前，对吗？）应用到它。

推荐阅读