Oracle XML聚合

考虑:

with data as (select 'MH' initials, to_date('01092019','ddmmyyyy') cal_date, 23 quantity from dual
              union all
              select 'MH' initials, to_date('02092019','ddmmyyyy') cal_date, 18 quantity from dual
              union all
              select 'MH' initials, to_date('03092019','ddmmyyyy') cal_date, 14 quantity from dual
              union all
              select 'RD' initials, to_date('01092019','ddmmyyyy') cal_date, 24 quantity from dual
              union all
              select 'RD' initials, to_date('02092019','ddmmyyyy') cal_date, 13 quantity from dual
              union all
              select 'RD' initials, to_date('03092019','ddmmyyyy') cal_date, 23 quantity from dual)
复制

我试图得到一个XML select语句，产生:

  
      1st Sep 2019 
      2nd Sep 2019 
      3rd Sep 2019 
   
      MH 23 
      MH 18 
      MH 14 
   
   
      RD 24 
      RD 13 
      RD 23 
   
  


复制

我似乎可以确定底部，但是正在努力在 标签中获取唯一的日期。

这是一种解决方法:

-在子查询中格式化数据。还使用dense_rank为每个日期分配行号
-使用它来调整MH和RD组。排名将为您提供MH & RD的行/日期和列
-将这些汇总到您的XML中:

with data as (
  select 'MH' initials, to_date('01092019','ddmmyyyy') cal_date, 23 quantity from dual
  union all
  select 'MH' initials, to_date('02092019','ddmmyyyy') cal_date, 18 quantity from dual
  union all
  select 'MH' initials, to_date('03092019','ddmmyyyy') cal_date, 14 quantity from dual
  union all
  select 'RD' initials, to_date('01092019','ddmmyyyy') cal_date, 24 quantity from dual
  union all
  select 'RD' initials, to_date('02092019','ddmmyyyy') cal_date, 13 quantity from dual
  union all
  select 'RD' initials, to_date('03092019','ddmmyyyy') cal_date, 23 quantity from dual
), vals as (
  select to_char ( cal_date , ' dd Mon yyyy ' ) cal_date,
         initials || ' ' || quantity as cell,
         initials grp,
         dense_rank () over ( order by cal_date ) rk
  from   data
), rws as ( 
  select * from vals
  pivot ( 
    min ( cell ) for grp in ( 'MH' mh, 'RD' rd ) 
  )
)
  select xmlelement ( 
           "rows",
           xmlforest (
             xmlagg ( xmlelement ( "column", cal_date ) order by rk ) as "head",
             xmlagg ( xmlelement ( "cell", mh ) order by rk ) as "row",
             xmlagg ( xmlelement ( "cell", rd ) order by rk ) as "row"
           )
         ) x
  from   rws;
  

  
    01 Sep 2019
    02 Sep 2019
    03 Sep 2019
  
  
    MH 23
    MH 18
    MH 14
  
  
    RD 24
    RD 13
    RD 23
  
复制

这假设每个日期都有一个MH & RD条目。如果没有，则需要根据最终文档中的显示方式进行调整。