嘿，我来帮你搞定这几个Oracle透视表的问题，刚好对12c R1的PIVOT特性挺熟的！咱们一个个来解决：

1. 如何添加行总计 #

行总计就是每个客户在所有酒店的预订总数，最简单的方式是在PIVOT查询结果里直接计算各酒店列的和。假设你的CUST_HOTEL_VIEW包含CUST_ID（客户ID）、HOTEL（酒店名称）、BOOKING_DATE（预订日期，用来计数）这类字段，示例代码如下：

SELECT 
    CUST_ID,
    NVL(HOTEL_A, 0) AS HOTEL_A,  -- 用NVL把NULL转成0，避免求和出错
    NVL(HOTEL_B, 0) AS HOTEL_B,
    NVL(HOTEL_C, 0) AS HOTEL_C,
    -- 计算行总计：所有酒店预订数相加
    (NVL(HOTEL_A, 0) + NVL(HOTEL_B, 0) + NVL(HOTEL_C, 0)) AS 行总计
FROM (
    -- 先按客户和酒店分组，统计每个客户在每个酒店的预订数
    SELECT CUST_ID, HOTEL, COUNT(*) AS BOOKINGS 
    FROM CUST_HOTEL_VIEW 
    GROUP BY CUST_ID, HOTEL
)
PIVOT (
    SUM(BOOKINGS)  -- 聚合函数，这里用SUM是因为分组后每个组的BOOKINGS是1，SUM和COUNT效果一样
    FOR HOTEL IN ('HOTEL_A' AS HOTEL_A, 'HOTEL_B' AS HOTEL_B, 'HOTEL_C' AS HOTEL_C)
);

2. 如何添加列总计 #

列总计是所有客户在单个酒店的预订总数，以及所有酒店的总预订数。可以用CTE先生成基础的PIVOT结果，再用UNION ALL追加总计行：

WITH PIVOT_BASE AS (
    SELECT 
        CUST_ID,
        NVL(HOTEL_A, 0) AS HOTEL_A,
        NVL(HOTEL_B, 0) AS HOTEL_B,
        NVL(HOTEL_C, 0) AS HOTEL_C,
        (NVL(HOTEL_A, 0) + NVL(HOTEL_B, 0) + NVL(HOTEL_C, 0)) AS 行总计
    FROM (
        SELECT CUST_ID, HOTEL, COUNT(*) AS BOOKINGS 
        FROM CUST_HOTEL_VIEW 
        GROUP BY CUST_ID, HOTEL
    )
    PIVOT (
        SUM(BOOKINGS)
        FOR HOTEL IN ('HOTEL_A' AS HOTEL_A, 'HOTEL_B' AS HOTEL_B, 'HOTEL_C' AS HOTEL_C)
    )
)
-- 先输出普通客户数据，再追加列总计行
SELECT * FROM PIVOT_BASE
UNION ALL
SELECT 
    '列总计' AS CUST_ID,  -- 标记总计行
    SUM(HOTEL_A) AS HOTEL_A,
    SUM(HOTEL_B) AS HOTEL_B,
    SUM(HOTEL_C) AS HOTEL_C,
    SUM(行总计) AS 行总计
FROM PIVOT_BASE
-- 让总计行排在最后
ORDER BY CASE WHEN CUST_ID = '列总计' THEN 1 ELSE 0 END, CUST_ID;

3. 如何统计客户已预订酒店数量 #

这个是统计每个客户有多少个不同的酒店有过预订（即预订数>0的酒店数量），可以用窗口函数在PIVOT前先计算好，或者在PIVOT后用CASE语句统计非零列：

方法1：PIVOT前用窗口函数（推荐） #

SELECT 
    CUST_ID,
    NVL(HOTEL_A, 0) AS HOTEL_A,
    NVL(HOTEL_B, 0) AS HOTEL_B,
    NVL(HOTEL_C, 0) AS HOTEL_C,
    (NVL(HOTEL_A, 0) + NVL(HOTEL_B, 0) + NVL(HOTEL_C, 0)) AS 行总计,
    已预订酒店数量
FROM (
    SELECT 
        CUST_ID,
        HOTEL,
        COUNT(*) AS BOOKINGS,
        -- 窗口函数：按客户分组，统计不同酒店的数量
        COUNT(DISTINCT HOTEL) OVER (PARTITION BY CUST_ID) AS 已预订酒店数量
    FROM CUST_HOTEL_VIEW 
    GROUP BY CUST_ID, HOTEL
)
PIVOT (
    SUM(BOOKINGS)
    FOR HOTEL IN ('HOTEL_A' AS HOTEL_A, 'HOTEL_B' AS HOTEL_B, 'HOTEL_C' AS HOTEL_C)
)
-- 去重，因为每个客户的多行数据里已预订酒店数量是一样的
GROUP BY CUST_ID, HOTEL_A, HOTEL_B, HOTEL_C, 已预订酒店数量;

方法2：PIVOT后用CASE统计 #

SELECT 
    *,
    -- 统计非零的酒店列数量
    CASE WHEN HOTEL_A > 0 THEN 1 ELSE 0 END +
    CASE WHEN HOTEL_B > 0 THEN 1 ELSE 0 END +
    CASE WHEN HOTEL_C > 0 THEN 1 ELSE 0 END AS 已预订酒店数量
FROM (
    SELECT 
        CUST_ID,
        NVL(HOTEL_A, 0) AS HOTEL_A,
        NVL(HOTEL_B, 0) AS HOTEL_B,
        NVL(HOTEL_C, 0) AS HOTEL_C,
        (NVL(HOTEL_A, 0) + NVL(HOTEL_B, 0) + NVL(HOTEL_C, 0)) AS 行总计
    FROM (
        SELECT CUST_ID, HOTEL, COUNT(*) AS BOOKINGS 
        FROM CUST_HOTEL_VIEW 
        GROUP BY CUST_ID, HOTEL
    )
    PIVOT (
        SUM(BOOKINGS)
        FOR HOTEL IN ('HOTEL_A' AS HOTEL_A, 'HOTEL_B' AS HOTEL_B, 'HOTEL_C' AS HOTEL_C)
    )
);

4. PIVOT的FOR HOTEL IN子句能否使用子查询？ #

很遗憾，Oracle 12c R1的静态PIVOT不支持在IN子句里用子查询，必须指定静态的字面量值（比如'HOTEL_A'、'HOTEL_B'）。如果你的酒店列表是动态变化的，需要用动态SQL来生成PIVOT查询：

DECLARE
    v_hotel_list VARCHAR2(1000);  -- 存储拼接好的酒店列表
    v_sql VARCHAR2(2000);         -- 存储动态生成的SQL语句
BEGIN
    -- 第一步：从视图中获取所有不同的酒店名称，拼接成PIVOT需要的格式
    SELECT LISTAGG('''' || HOTEL || ''' AS ' || HOTEL, ', ') WITHIN GROUP (ORDER BY HOTEL)
    INTO v_hotel_list
    FROM (SELECT DISTINCT HOTEL FROM CUST_HOTEL_VIEW);
    
    -- 第二步：构建完整的动态PIVOT SQL
    v_sql := '
        SELECT 
            CUST_ID,
            ' || v_hotel_list || ',
            -- 动态计算行总计：把所有酒店列相加
            ' || LISTAGG('NVL(' || HOTEL || ', 0)', ' + ') WITHIN GROUP (ORDER BY HOTEL) || ' AS 行总计
        FROM (
            SELECT CUST_ID, HOTEL, COUNT(*) AS BOOKINGS 
            FROM CUST_HOTEL_VIEW 
            GROUP BY CUST_ID, HOTEL
        )
        PIVOT (
            SUM(BOOKINGS)
            FOR HOTEL IN (' || v_hotel_list || ')
        )';
    
    -- 输出生成的SQL（可以复制到SQL*Plus里执行）
    DBMS_OUTPUT.PUT_LINE(v_sql);
    
    -- 如果要直接执行并返回结果，可以用REF CURSOR：
    -- OPEN :your_ref_cursor FOR v_sql;
END;
/

执行这段PL/SQL后，会输出完整的动态PIVOT查询语句，你可以直接复制执行，或者用REF CURSOR在程序里返回结果。

内容的提问来源于stack exchange，提问作者Richa