0

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): enter image description here

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 |
+--------------+---------------+--------+-------------+--------+--------------+--------+-------------+------+
Improve this question
3
  • In SQL you decide, what columns you fill, when you write the query. It is afaik impossible to get 15 colums because there are 15 different hotels in your result set. I finish such tasks with a table calculation program Commented May 30, 2018 at 23:11
  • F.e. Excel Pivot Table Commented May 30, 2018 at 23:13
  • @am2, thanks for the prompt response. I appreciate. Assuming table only have 3 or 4 hotels, in that sense is it still impossible to have this with sql query? I would like to know with SQL how to achieve multiple row header with grouping. If the query works for my sample data provided, that's more than enough for me. Commented May 31, 2018 at 1:12

1 Answer 1

Reset to default
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) |
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks for the reply. Am sorry if my question was confusing. Answser you suggested is pretty close but I want header as merged. Since am not able to post table in comment. Am updating the OP under section "FINAL TABLE OUTPUT"
This is not possible to do in ordinary SQL, you need to do it at presentation layer of your application (whatever it is - java, c#, excel etc), not at the database side..
Thanks for your feedback and answer. I appreciate. Can you please let me know if I can use sub query in this line: FOR HOTEL IN ('Beverly Hills','Royal Palms','Ritz-Carlton'). I tried subquery and getting an error
No, a subquery canot be used, pivot is a static-SQL. You can use subqueries in XML PIVOT, see this article: oracle.com/technetwork/articles/sql/11g-pivot-097235.html, but this generates XML results, not table-like resultset. You can also use dynamic SQL to generate dynamically query, but this is a huge topic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.