Oracle SQL - Pivot table rows to column and use sub query in pivot

Question

Am working on Oracle 12c R1 db and have a sample view with sample data as below:
View Name: CUST_HOTEL_VIEW

+----------------+---------------+---------------+
|    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        |
+----------------+---------------+---------------+

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:

+----------------+-------------+---------------+--------------+-------------+----------+
|    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 |
+----------------+-------------+---------------+--------------+-------------+----------+

I tried below query to generate pivot data

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

I would like to know:
1. How to include Row Grand Total
2. How to include Column Grand Total
3. How to include Number of Booked hotels and
3. Is it possible to write subquery inside PIVOT FOR HOTEL IN clause. (I tried subquery but getting error)

I appreciate any help on this.

Thanks,
Richa

Wow amazing.. Thanks Barbaros Özhan, Gordon Linoff, Shrek and G.Arima. You guys are awesome. I could never imagine one problem had so many flavors of solution. — Richa
– Richa, Commented May 27, 2018 at 19:48
I highly appreciate all your kind help and most of all your precious time. — Richa
– Richa, Commented May 27, 2018 at 20:02

Gordon Linoff · Accepted Answer · 2018-05-27 20:19:44Z

5

Just use conditional aggregation:

SELECT COALESCE(customer, 'Grand Total') as customer,
       SUM(CASE WHEN Hotel = 'Royal Palms' THEN 1 ELSE 0 END) as "Royal Palms",
       SUM(CASE WHEN Hotel = 'Beverly Hills' THEN 1 ELSE 0 END) as "Beverly Hills",       
       SUM(CASE WHEN Hotel = 'Ritz-Carlton' THEN 1 ELSE 0 END) as "Ritz-Carlton" ,
       COUNT(*) as "Grand Total",
       COUNT(Booked_Status) as "Num Booked"
FROM CUST_HOTEL_VIEW
GROUP BY ROLLUP(CUSTOMER)
ORDER BY CUSTOMER;

Conditional aggregation is much more flexible then pivot. Personally, I see no reason for the pivot syntax: it does one thing well, but is not a building block the way tradition SQL statements are.

ROLLUP() is also quite helpful. You can also use:

GROUP BY GROUPING SETS ( (CUSTOMER), () )

edited May 27, 2018 at 20:19

answered May 26, 2018 at 1:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Richa Over a year ago

Thank you so much @Gordon Linoff. You made complex problem to look simple. Amazing. I tried your code and am not able to see Label "Grand Total" for last row. But everything worked flawless.

Gordon Linoff Over a year ago

@Richa . . . The quick and dirty way is to use SELECT COALESCE(customer, 'Grand Total') as customer, . . . .

Richa Over a year ago

Fantastic. It worked. Brilliant work. Thanks again. I appreciate it.

Richa Over a year ago

Am trying to replace results returned with 0 as null or dash (-) without parenthesis. Within the case statement I entered "" in place of 0. And again tried with '-' in place of 0. Since whole case statement is enclosed by SUM, SQL is throwing error. Any help on this please?

Richa Over a year ago

As per the end results shown in my OP, am trying to display results with hotels as blanks if there is 0 value and under the "# Booked" column, trying to replace 0 with '-'

|

shrek · Accepted Answer · 2018-05-25 23:36:19Z

1

There might be an easier solution but this should help you get started -

WITH A AS (
    SELECT
        *
    FROM
        (
            SELECT
                CUSTOMER,
                HOTEL,
                BOOKED_STATUS
            FROM
                TABLE1
        )
            PIVOT ( COUNT ( HOTEL )
                FOR HOTEL
                IN ( 'ROYAL PALMS' AS "ROYAL PALMS",'BEVERLY HILLS' AS "BEVERLY HILLS",'RITZ-CARLTON' AS "RITZ-CARLTON" )
            )
),B AS (
    SELECT
        CUSTOMER,
        "ROYAL PALMS",
        "BEVERLY HILLS",
        "RITZ-CARLTON",
        SUM("ROYAL PALMS" + "BEVERLY HILLS" + "RITZ-CARLTON") AS "GRAND TOTAL"
    FROM
        A
    GROUP BY
        CUSTOMER,
        "ROYAL PALMS",
        "BEVERLY HILLS",
        "RITZ-CARLTON"
),C AS (
    SELECT
        CUSTOMER,
        SUM("ROYAL PALMS" + "BEVERLY HILLS" + "RITZ-CARLTON") AS NBOOK
    FROM
        A
    WHERE
        BOOKED_STATUS IS NOT NULL
    GROUP BY
        CUSTOMER
),D AS (
    SELECT
        B.CUSTOMER,
        SUM("ROYAL PALMS") AS "ROYAL PALMS",
        SUM("BEVERLY HILLS") AS "BEVERLY HILLS",
        SUM("RITZ-CARLTON") AS "RITZ-CARLTON",
        SUM("GRAND TOTAL") AS "GRAND TOTAL",
        NVL(C.NBOOK,0) AS NBOOK
    FROM
        B,
        C
    WHERE
        B.CUSTOMER   = C.CUSTOMER (+)
    GROUP BY
        B.CUSTOMER,
        NVL(C.NBOOK,0)
) SELECT
    *
FROM
    D
UNION
SELECT
    'GRAND TOTAL' AS CUSTOMER,
    SUM("ROYAL PALMS"),
    SUM("BEVERLY HILLS"),
    SUM("RITZ-CARLTON"),
    SUM("GRAND TOTAL") AS "GRAND TOTAL",
    SUM(NBOOK) AS NBOOK
FROM
    D
ORDER BY 5,1;

Output -

"CUSTOMER","ROYAL PALMS","BEVERLY HILLS","RITZ-CARLTON","GRAND TOTAL","NBOOK"
"MARILYN LAWSON",1,1,0,2,0
"JOHN SMITH",1,1,1,3,1
"SARAH ELLIOT",2,0,1,3,2
"GRAND TOTAL",4,2,2,8,3

answered May 25, 2018 at 23:36

shrek

8876 silver badges12 bronze badges

2 Comments

Richa Over a year ago

Thank you for providing the query. Am getting error if I add another hotel as "Test Hyatt/Hilton" and include that inside all other statements. I also tried to debug the code and only first query within the "WITH" clause executes without error but the other derived queries throws error. ORA-00904: "Test Hyatt/Hilton": invalid identifier

shrek Over a year ago

Could be because of the / in the hotel name. Can you try escaping it with \ ? ......as in "Test Hyatt\/Hilton"

G.Arima · Accepted Answer · 2018-05-26 00:56:44Z

1

Try this out:

1) To include row total union can be used

2) To include column total sum function is used in the first table (p1) : the same code written by you with the sum function

3) To include number of booked hotels, another pivot is required, which is second table (p2)

Step1: Made a table p1 with customer,different hotels, and their grand total (column total)

create table p1 as
SELECT customer,RoyalPalms,BeverlyHills,RitzCarlton,sum(RoyalPalms + BeverlyHills + RitzCarlton) as GrandTotal  FROM
(
  SELECT CUSTOMER, HOTEL
  FROM CUST
)
PIVOT
( COUNT(HOTEL)
  FOR HOTEL IN ('Royal Palms' as RoyalPalms,'Beverly Hills' as BeverlyHills,
                'Ritz-Carlton' as RitzCarlton) ) 
group by customer,RoyalPalms,BeverlyHills,RitzCarlton
order by customer;

Step2: Make a table p2 with customer and booked hotels

create table p2 as
SELECT *  FROM
(
  SELECT customer,booked_status
  FROM cust
)
pivot 
 ( count(booked_status) 
  for booked_status in ('Booked' as Booked));

Step3: Join table p1 and p2

Step4: For row total, take the union of table made in Step3 with the row totals calculated by summary function

(SELECT a.*,b.booked
 from p1 a 
 left join
 p2 b
 on a.customer = b.customer) 
union all
SELECT 'GrandTotal', Sum(RoyalPalms),sum(BeverlyHills),sum(RitzCarlton),
sum(GrandTotal),sum(booked) From test;

Output:

+----------------+-------------+---------------+--------------+-------------+----------+
|    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 |
+----------------+-------------+---------------+--------------+-------------+----------+

Let me know in case of any further queries/explanation required.

answered May 26, 2018 at 0:56

G.Arima

1,1711 gold badge6 silver badges13 bronze badges

2 Comments

Richa Over a year ago

Thanks @G.Arima for the query. I do have view created based on different tables. If I create new tables for this, am not sure if data grows, I will have to recreate tables. Sorry if my question is dumb.

G.Arima Over a year ago

I wrote the solution that even if your data grows, one line each for distinct customer will add,that's it. N no need to be sorry. Happy to help :)

Collectives™ on Stack Overflow

Oracle SQL - Pivot table rows to column and use sub query in pivot

3 Answers 3

6 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related