Select
   TO_NUMBER(q_cc.quarter) quarter,
   Rtrim(Xmlagg(Xmlelement(E,Q_Cc.Calendar_Month,'; ').Extract('//text()') Order By Q_Cc.Month_No),'; ')
     Quarter_Months,
   Rtrim(Xmlagg(Xmlelement(E,Q_Cc.Month_Days,'; ').Extract('//text()') Order By Q_Cc.Month_No),'; ')
   Month_Days,
     TO_NUMBER(SUM(month_days)) quarter_days
   From
   (SELECT
     level month_no,
     NTILE(4) OVER (ORDER BY level) As Quarter,
     To_Char(Add_Months(Trunc(Sysdate,'year'),Level-1),'Mon') Calendar_Month,
     Last_Day(Add_Months(Trunc(Sysdate,'year'),Level-1))-
       ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)+1 month_days
     FROM
     DUAL CONNECT BY level <= 12
     ) q_cc
   Group BY
    TO_NUMBER(q_cc.quarter);

The months and days of the month in the calendar data shown below can be grouped into quarter based months and corresponding days using the Oracle XML function xmlagg() in a summary query.

The data separator/delimiter specified in this example query is "; ". It can be replaced by "," or "|" or any other delimiter. The other alternative to this is to write a function (or table function) that will mathematically group data with delimiter based on the grouping value - in this case the Quarter.

This XML function is useful in reports that can list multiple dates that a single invoice shipped on, office visits of a patient for a single medical condition etc.

---

  72961