Oracle SQL-将数据透视表中的行转换为列并在数据透视表中使用子查询

一尘不染

Oracle SQL-将数据透视表中的行转换为列并在数据透视表中使用子查询

sql

我正在使用Oracle 12c R1 db并具有以下示例数据的示例视图：
视图名称： 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和每位客户预订的酒店数量低于数据透视表的输出：

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

我尝试在下面的查询中生成数据透视表

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.如何包括行总计
2.如何包括列总计
3.如何包括预订酒店的数量以及
3.是否可以在PIVOT FOR HOTEL IN子句中编写子查询。（我尝试了子查询，但收到错误），

我对此表示感谢。

谢谢，理
查

阅读 164

2021-03-08

共1个答案

一尘不染

只需使用条件聚合：

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语句的构造块。

ROLLUP()也很有帮助。您还可以使用：

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

2021-03-08