Markus Markus - 1 month ago 10
SQL Question

Oracle Coalesce two rows in one

I plan to coalesce two (many) rows into one line. My data looks like this

╔══════════╦════════╦═══════════╦═══════════╦════════════╗
║ ReportID ║ Item ║ StartDt ║ EndDt ║ Statement ║
╠══════════╬════════╬═══════════╬═══════════╬════════════╣
║ 1 ║ 3000 ║ 11-Mar-16 ║ (null) ║ Remark ║
╠══════════╬════════╬═══════════╬═══════════╬════════════╣
║ 1 ║ 3001 ║ 11-Mar-16 ║ 13-Mar-16 ║ (null) ║
╠══════════╬════════╬═══════════╬═══════════╬════════════╣
║ 2 ║ 4002 ║ 24-May-16 ║ 27-May-16 ║ Remark1 ║
╠══════════╬════════╬═══════════╬═══════════╬════════════╣
║ 2 ║ 4003 ║ 24-May-16 ║ 28-May-16 ║ Remark1 ║
╚══════════╩════════╩═══════════╩═══════════╩════════════╝


I would love to merge by
ReportID
and use different rules:


  • Use highest
    endDt

  • Use
    Statement
    that is not
    null

  • Use
    Statement
    of the record with the highest
    Item



Hence, the result should be

╔══════════╦═══════════╦═══════════╦════════════╗
║ ReportID ║ StartDt ║ EndDt ║ Statement ║
╠══════════╬═══════════╬═══════════╬════════════╣
║ 1 ║ 11-Mar-16 ║ 13-Mar-16 ║ Remark ║
╠══════════╬═══════════╬═══════════╬════════════╣
║ 2 ║ 24-May-16 ║ 28-May-16 ║ Remark1 ║
╚══════════╩═══════════╩═══════════╩════════════╝


I need to achieve this with plain SQL and cannot write PL-SQL routines.

Data



SELECT 1 as ReportID, 3000 as Item, TO_DATE('11-03-2016') as StartDt, TO_DATE(NULL) as EndDt, 'Remark' as Statement FROM DUAL
UNION
SELECT 1, 3001, TO_DATE('11-03-2016'), TO_DATE('13-03-2016'), NULL FROM DUAL
UNION
SELECT 2, 4002, TO_DATE('24-05-2016'), TO_DATE('27-05-2016'), 'Remark1' FROM DUAL
UNION
SELECT 2, 4003, TO_DATE('24-05-2016'), TO_DATE('28-05-2016'), 'Remark1' FROM DUAL

Answer

You want one row per reportid, so you'd group by it. Then use the appropriate aggregation functions (which is KEEP FIRST/LAST for the statement column) to get the desired values:

select 
  reportid,
  max(startdt) as startdt,
  max(enddt) as enddt,
  max(statement) keep (dense_rank last 
                         order by case when statement is null then 1 else 2 end, item
                      ) as statement
from mytable
group by reportid
order by reportid;