title: Oracle SQL - Pivot table rows to column and use sub query in pivot
body: <p>Am working on Oracle 12c R1 db and have a sample view with sample data as below:
  <br>View Name: <strong>CUST_HOTEL_VIEW</strong></p>
  
  <pre><code>+----------------+---------------+---------------+
  |    Customer    |     Hotel     | Booked Status |
  +----------------+---------------+---------------+
  | John Smith     | Beverly Hills | Booked        |
  | John Smith     | Royal Palms   |               |
  | Marilyn Lawson | Beverly Hills |               |
  | John Smith     | Ritz-Carlton  |               |
  | Marilyn Lawson | Royal Palms   |               |
  | Sarah Elliot   | Royal Palms   |               |
  | Sarah Elliot   | Ritz-Carlton  | Booked        |
  | Sarah Elliot   | Royal Palms   | Booked        |
  +----------------+---------------+---------------+
  </code></pre>
  
  <p>From the data above, am trying to get below pivot output with Row Grand Total, Column Grand Total and Number of Hotels booked per customer:</p>
  
  <pre><code>+----------------+-------------+---------------+--------------+-------------+----------+
  |    Customer    | Royal Palms | Beverly Hills | Ritz-Carlton | Grand Total | # Booked |
  +----------------+-------------+---------------+--------------+-------------+----------+
  | John Smith     |           1 |             1 |            1 |           3 |        1 |
  | Marilyn Lawson |           1 |             1 |              |           2 |        - |
  | Sarah Elliot   |           2 |               |            1 |           3 |        2 |
  | Grand Total    |           4 |             2 |            2 |           8 |        3 |
  +----------------+-------------+---------------+--------------+-------------+----------+
  </code></pre>
  
  <p>I tried below query to generate pivot data</p>
  
  <pre><code>SELECT * FROM
  (
    SELECT CUSTOMER, HOTEL
    FROM CUST_HOTEL_VIEW
  )
  PIVOT
  (
    COUNT(HOTEL)
    FOR HOTEL IN ('Royal Palms' as "Royal Palms",'Beverly Hills' as "Beverly Hills",'Ritz-Carlton' as "Ritz-Carlton")
  )
  ORDER BY CUSTOMER
  </code></pre>
  
  <p>I would like to know:<br>
  1. How to include Row Grand Total<br>
  2. How to include Column Grand Total<br>
  3. How to include Number of Booked hotels and <br>
  3. Is it possible to write subquery inside PIVOT FOR HOTEL IN clause. (I tried subquery but getting error)<br>
  <br>
  I appreciate any help on this. </p>
  
  <p>Thanks,<br>
  Richa</p>