I'm getting output for pivot xml as
<pivotset><item><column name = "BUSINESS_DATE">2018-10-29<column name = "SUM(DATA_COUNT)">125<item><column name = "BUSINESS_DATE">2018-10-30<column name = "SUM(DATA_COUNT)">12<item><column name = "BUSINESS_DATE">2018-10-31<column name = "SUM(DATA_COUNT)">1
In this the business_date is getting populated in ascending order.
Can anyone please let me know how to generate the pivot xml file in which the business_date would be in desc_order.
the pivot xml will be generated in business_Date_xml here in which the business_date is in ascending order which should be desc.
What I have tried:
with pivot_data as
(
SELECT functional_area,business_date, data_count
FROM WS_PEAK_SEASON_STATISTICS
)
select functional_area, business_date_xml,
extractvalue(business_date_xml,'/PivotSet/item[1]/column[2]//text()') vdate,
extractvalue(business_date_xml,'/PivotSet/item[2]/column[2]//text()') vdate1,
extractvalue(business_date_xml,'/PivotSet/item[3]/column[2]//text()') vdate2,
extractvalue(business_date_xml,'/PivotSet/item[4]/column[2]//text()') vdate3,
extractvalue(business_date_xml,'/PivotSet/item[5]/column[2]//text()') vdate4,
extractvalue(business_date_xml,'/PivotSet/item[6]/column[2]//text()') vdate5,
extractvalue(business_date_xml,'/PivotSet/item[7]/column[2]//text()') vdate6
from pivot_data
Pivot xml(sum(data_count) for business_date in (SELECT business_date
FROM WS_PEAK_SEASON_STATISTICS, period where business_date>(vdate-7)))