Oracle SQL-将数据透视表中的行转换为列并在数据透视表中使用子查询 [英] Oracle SQL - Pivot table rows to column and use sub query in pivot

问题描述

我正在Oracle 12c R1 db上工作，并具有一个示例视图，其中包含以下示例数据:
视图名称: CUST_HOTEL_VIEW

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

根据上面的数据，我试图通过Row Grand Total，Column Grand Total和每位客户预订的酒店数量低于数据透视输出:

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

我想知道:
1.如何包含Row Grand Total
2.如何包括列总计总计
3.如何包括预订酒店的数量和
3.是否可以在PIVOT FOR HOTEL IN子句中编写子查询. (我尝试了子查询，但出现错误)

我对此表示感谢.

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.

谢谢，
里查

推荐答案

只需使用条件聚合:

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;

条件聚合比pivot灵活得多.就我个人而言，我没有理由使用pivot语法:它做得很好，但不是传统SQL语句的构造块.

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()也很有帮助.您还可以使用:

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

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

这篇关于Oracle SQL-将数据透视表中的行转换为列并在数据透视表中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！