Consider:
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)
I'm trying to get an XML select statement that produces:
<rows>
<head>
<column> 1st Sep 2019 </column>
<column> 2nd Sep 2019 </column>
<column> 3rd Sep 2019 </column
</head>
<row>
<cell> MH 23 </cell>
<cell> MH 18 </cell>
<cell> MH 14 </cell>
</row>
<row>
<cell> RD 24 </cell>
<cell> RD 13 </cell>
<cell> RD 23 </cell>
</row>
</rows>
I seem to be able to get the bottom part ok but am struggling with getting the unique dates in the <head> tag.
Here's one way to approach it:
- Format the data in a subquery. Also assign a row number to each date using dense_rank
- Use this to pivot the MH and RD groups. The rank will give you a row/date and columns for MH & RD
- Aggregate these all up into your 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;
<rows>
<head>
<column>01 Sep 2019</column>
<column>02 Sep 2019</column>
<column>03 Sep 2019</column>
</head>
<row>
<cell>MH 23</cell>
<cell>MH 18</cell>
<cell>MH 14</cell>
</row>
<row>
<cell>RD 24</cell>
<cell>RD 13</cell>
<cell>RD 23</cell>
</row>
</rows>This assumes that there's an MH & RD entry for every date. If there isn't you'll need to tweak this depending on how this should appear in the final document.