Oracle SQL - Conditional aggregation pivot with multiple header

Question

This question is much enhanced version to my previous post and hope I get some help here. I have below table/View with sample or dummy data:
TABLE/VIEW name: CUST_HOTEL_VIEW

+----+----------------+---------------+---------------+--------+---------------+
| ID |    Customer    |     Hotel     | Booked_Status | Budget | Booked_Amount |
+----+----------------+---------------+---------------+--------+---------------+
|  1 | John Smith     | Beverly Hills | Booked        |   2000 |           500 |
|  2 | John Smith     | Royal Palms   | Cancelled     |   3000 |               |
|  3 | Marilyn Lawson | Beverly Hills |               |    500 |               |
|  4 | John Smith     | Ritz-Carlton  |               |    250 |               |
|  5 | Marilyn Lawson | Royal Palms   |               |    700 |               |
|  6 | Sarah Elliot   | Royal Palms   | Cancelled     |   1500 |               |
|  7 | Sarah Elliot   | Ritz-Carlton  | Booked        |   2000 |          1500 |
|  8 | Sarah Elliot   | Royal Palms   | Booked        |   2500 |          1000 |
+----+----------------+---------------+---------------+--------+---------------+

Need help to get below output with multiple header i.e., to get count of customer and sum of budget per hotel (Below is the output from excel pivot table):

Am using Oracle 12c R1 db and if I get query with conditional aggregation that helps; as I found this simpler and much easier to understand.

I tried below query to get count and booked amount:

SELECT COALESCE(CUSTOMER, 'Grand Total') as " " ,
       (case when COUNT(booked_status) != 0 then count(booked_status) else null end) as "# Booked",
sum(case when booked_status = 'Booked' THEN booking_amount ELSE null END) as "Booked Amount"
FROM CUST_HOTEL_VIEW
GROUP BY ROLLUP(CUSTOMER)
order by CUSTOMER

and got below output:

+--------------+----------+---------------+
|              | # Booked | Booked Amount |
+--------------+----------+---------------+
| John Smith   |        1 |           500 |
| Sarah Elliot |        2 |          2500 |
| Grand Total  |        3 |          3000 |
+--------------+----------+---------------+

But was unable to get output with merged header.

I appreciate any help with this.

Thanks,
Richa

UPDATE: Adding final output table section below: FINAL OUTPUT TABLE:

+--------------+---------------+--------+-------------+--------+--------------+--------+-------------+------+
|   CUSTOMER   |      Beverly Hills     |      Royal Palms     |      Ritz-Carlton     |      Grand Total   |
+--------------+---------------+--------+-------------+--------+--------------+--------+-------------+------+
|              | Count         | Sum    | Count       | Sum    | Count        | Sum    | Count       | Sum  |
+--------------+---------------+--------+-------------+--------+--------------+--------+-------------+------+
| Sarah Elliot | 0             | (null) | 1           | 2500   | 1            | 2000   | 2           | 4500 |
| John Smith   | 1             | 2000   | 0           | (null) | 0            | (null) | 1           | 2000 |
| Grand Total  | 1             | 2000   | 1           | 2500   | 1            | 2000   | 3           | 6500 |
+--------------+---------------+--------+-------------+--------+--------------+--------+-------------+------+

Answer 1

From the question's description I can not infer what exactly is the requirement, but it seems to me that you are looking for something like that:

---

Demo: http://sqlfiddle.com/#!4/dbd49/7

SELECT *
FROM (
  SELECT CUSTOMER, HOTEL, BUDGET
  FROM Table1
  WHERE BOOKED_STATUS = 'Booked'
)
PIVOT (
  count(*) as cnt, sum(BUDGET) as budget
  FOR HOTEL IN ('Beverly Hills','Royal Palms','Ritz-Carlton')
 )

---

|     CUSTOMER | 'Beverly Hills'_CNT | 'Beverly Hills'_BUDGET | 'Royal Palms'_CNT | 'Royal Palms'_BUDGET | 'Ritz-Carlton'_CNT | 'Ritz-Carlton'_BUDGET |
|--------------|---------------------|------------------------|-------------------|----------------------|--------------------|-----------------------|
| Sarah Elliot |                   0 |                 (null) |                 1 |                 2500 |                  1 |                  2000 |
|   John Smith |                   1 |                   2000 |                 0 |               (null) |                  0 |                (null) |