Using PIVOT with multiple unions

Question

Currently building a report and need to pivot my data to get the columns and rows reversed. Data is currently displayed as such:

WEEK_DATE	HOTEL	AIR_FARE
22-Aug	0	0
23-Aug	0	0
24-Aug	0	0
25-Aug	102	0
26-Aug	0	0

Desired format:

Expense_Category	22-Aug	23-Aug	24-Aug	25-Aug	26-Aug
HOTEL	0	0	0	102	0
AIR FARE	0	0	0	0	0

Query:

SELECT * FROM (
SELECT
TO_CHAR(TRUNC(exr.expense_report_date, 'D'),'MM/DD') Week_Date,
(SELECT CASE when ee.REIMBURSABLE_AMOUNT is null then 0 else ee.REIMBURSABLE_AMOUNT end as "RE_1"
FROM exm_expense_reports exr, exm_expenses ee, exm_expense_types eet 
WHERE exr.expense_report_id = ee.expense_report_id
and ee.EXPENSE_TYPE_ID  = eet.EXPENSE_TYPE_ID 
and to_char(EE.END_DATE, 'MM/DD') =  to_char(TRUNC(exr.expense_report_date, 'D'),'MM/DD') 
and EET.name = 'Hotel'
and exr.EXPENSE_REPORT_NUM  = 'EXP000009057987') "HOTEL",
(SELECT CASE when ee.REIMBURSABLE_AMOUNT is null then 0 else ee.REIMBURSABLE_AMOUNT end as "RE_1"  
FROM exm_expense_reports exr, exm_expenses ee, exm_expense_types eet 
WHERE exr.expense_report_id = ee.expense_report_id
and ee.EXPENSE_TYPE_ID  = eet.EXPENSE_TYPE_ID 
and to_char(EE.END_DATE, 'MM/DD') =  to_char(TRUNC(exr.expense_report_date, 'D'),'MM/DD') 
and EET.name = 'Air Fare'
and exr.EXPENSE_REPORT_NUM  = 'EXP000009057987') "AIR FARE"
from exm_expense_reports exr WHERE exr.EXPENSE_REPORT_NUM  = 'EXP000009057987'

UNION
SELECT  
TO_CHAR(TRUNC(exr.expense_report_date, 'D')+1,'MM/DD') Week_Date,
(SELECT CASE when ee.REIMBURSABLE_AMOUNT is null then 0 else ee.REIMBURSABLE_AMOUNT end as "RE_1"  
FROM exm_expense_reports exr, exm_expenses ee, exm_expense_types eet 
WHERE exr.expense_report_id = ee.expense_report_id
and ee.EXPENSE_TYPE_ID  = eet.EXPENSE_TYPE_ID 
and to_char(EE.END_DATE, 'MM/DD') =  to_char(TRUNC(exr.expense_report_date, 'D')+1,'MM/DD') 
and EET.name = 'Hotel'
and exr.EXPENSE_REPORT_NUM  = 'EXP000009057987') "HOTEL",
(SELECT CASE when ee.REIMBURSABLE_AMOUNT is null then 0 else ee.REIMBURSABLE_AMOUNT end as "RE_1"  
FROM exm_expense_reports exr, exm_expenses ee, exm_expense_types eet 
WHERE exr.expense_report_id = ee.expense_report_id
and ee.EXPENSE_TYPE_ID  = eet.EXPENSE_TYPE_ID 
and to_char(EE.END_DATE, 'MM/DD') =  to_char(TRUNC(exr.expense_report_date, 'D')+1,'MM/DD') 
and EET.name = 'Air Fare'
and exr.EXPENSE_REPORT_NUM  = 'EXP000009057987') "AIR FARE"
from exm_expense_reports exr WHERE exr.EXPENSE_REPORT_NUM  = 'EXP000009057987'

(Date parameter repeated for 6 total unions)

When testing in Oracle I've gotten "ORA-01748: only simple column names allowed here" error. I'm new to pivots so not sure I'm doing this correctly. Any help would be appreciated, thank you!

score 0 · Answer 1 · answered Sep 22 '21 at 15:11

I'm not sure what your query has to do with the question. It is much more complicated that what you describe.

But if you have a table or result set in the format you specify, you can unpivot and reaggregate:

select x.expense_category,
       sum(case when t.week_date = '22-AUG' then x.amount else 0 end) as week_aug_22,
       sum(case when t.week_date = '23-AUG' then x.amount else 0 end) as week_aug_23,
       . . .
from t cross join lateral
     (select 'HOTEL' as expense_category, hotel as amount from dual union all
      select 'AIR FARE' as expense_category, air_fare as amount from dual
     ) x

Using PIVOT with multiple unions

1 Answers1