I am new to PIVOT queries. I was referencing the following discussion but ran into a problem. https://forums.oracle.com/message/9393302
When I added the XML to format the data, I started getting nulls rather than the sums I was looking for.
WITH PIVOT_DATA AS (
SELECT * FROM (
SELECT PRIME_PART, REGION, FCST_PERIOD, FINAL_FORECAST
FROM XYZ WHERE FCST_PERIOD >= last_day(trunc(sysdate))+1 and FCST_PERIOD <= last_day(add_months(sysdate,12)) AND PRIME_PART IN ('BLAH')
)
PIVOT XML (SUM (FINAL_FORECAST) FOR FCST_PERIOD IN (SELECT DISTINCT FCST_PERIOD FROM XYZ))
)
SELECT PRIME_PART, REGION,
EXTRACTVALUE(FCST_PERIOD_XML,'/PIVOTSET/ITEM[1]/COLUMN[2FCST_PERIOD1,
EXTRACTVALUE(FCST_PERIOD_XML,'/PIVOTSET/ITEM[2]/COLUMN[2FCST_PERIOD2,
EXTRACTVALUE(FCST_PERIOD_XML,'/PIVOTSET/ITEM[3]/COLUMN[2FCST_PERIOD3
FROM PIVOT_DATA;
RESULTS IN:
| PRIME_PART | REGION | FCST_PERIOD1 | FCST_PERIOD2 | FCST_PERIOD3 |
| BLAH | MIDWEST | (NULL) | (NULL) | (NULL) |
| BLAH | WEST-NV | (NULL) | (NULL) | (NULL) |
| BLAH | SOUTHEAST | (NULL) | (NULL) | (NULL) |
| BLAH | WEST-CA | (NULL) | (NULL) | (NULL) |
| BLAH | SOUTHWEST | (NULL) | (NULL) | (NULL) |
| BLAH | EAST | (NULL) | (NULL) | (NULL) |
The second part of my question is how would I do a group by when using a pivot query. When I am done, I will have multiple parts and would like to group by REGION.
Thanks!