Oracle 12c R1 多表头条件聚合透视查询需求

Got it, let's tackle this multi-header pivot table requirement using conditional aggregation in Oracle 12c R1. Your goal is to get a breakdown of customer counts and budget sums per hotel, plus grand totals—exactly like the Excel pivot table you referenced.

Solution SQL Query #

Here's the query that will generate the data structure matching your final output, using conditional aggregation (clean and maintainable as you requested):

SELECT
    COALESCE(CUSTOMER, 'Grand Total') AS "CUSTOMER",
    -- Beverly Hills metrics
    COUNT(CASE WHEN HOTEL = 'Beverly Hills' THEN 1 END) AS "Beverly Hills_Count",
    SUM(CASE WHEN HOTEL = 'Beverly Hills' THEN BUDGET END) AS "Beverly Hills_Sum",
    -- Royal Palms metrics
    COUNT(CASE WHEN HOTEL = 'Royal Palms' THEN 1 END) AS "Royal Palms_Count",
    SUM(CASE WHEN HOTEL = 'Royal Palms' THEN BUDGET END) AS "Royal Palms_Sum",
    -- Ritz-Carlton metrics
    COUNT(CASE WHEN HOTEL = 'Ritz-Carlton' THEN 1 END) AS "Ritz-Carlton_Count",
    SUM(CASE WHEN HOTEL = 'Ritz-Carlton' THEN BUDGET END) AS "Ritz-Carlton_Sum",
    -- Grand Total metrics
    COUNT(*) AS "Grand Total_Count",
    SUM(BUDGET) AS "Grand Total_Sum"
FROM CUST_HOTEL_VIEW
GROUP BY ROLLUP(CUSTOMER)
ORDER BY 
    CASE WHEN CUSTOMER IS NULL THEN 1 ELSE 0 END, -- Push Grand Total to the bottom
    CUSTOMER;

How It Works #

Let's break down the key parts:

1. Conditional Aggregation: For each hotel, we use CASE statements to filter rows for that hotel before applying COUNT and SUM. This lets us calculate metrics per hotel directly in the select clause.
   • COUNT(CASE WHEN HOTEL = 'X' THEN 1 END): Counts how many times the customer has a record for hotel X (returns 0 if none).
   • SUM(CASE WHEN HOTEL = 'X' THEN BUDGET END): Sums the budget for that hotel (returns NULL if no records, matching your final output).
2. ROLLUP for Totals: GROUP BY ROLLUP(CUSTOMER) generates two levels of grouping:
   • One row per unique customer
   • A final "Grand Total" row (where CUSTOMER is NULL, which we replace with 'Grand Total' using COALESCE)
3. Sorting: The ORDER BY clause ensures the Grand Total stays at the bottom, while individual customers are sorted alphabetically.

Matching Your Final Output #

When you run this query, the result columns map directly to your multi-header structure:

• The first column is CUSTOMER (including Grand Total)
• For each hotel, you get two columns: [Hotel Name]_Count and [Hotel Name]_Sum
• The last two columns are the Grand Total's count and sum

If you need to render this as a true multi-header table (e.g., in a reporting tool or Excel), you can group the columns by hotel name:

• Group "Beverly Hills_Count" and "Beverly Hills_Sum" under a parent header "Beverly Hills"
• Repeat for the other hotels and the Grand Total

Notes #

• This query includes all records (regardless of Booked_Status), which aligns with your final output. If you ever need to filter for only booked/cancelled statuses, you can add a WHERE clause or adjust the CASE statements (e.g., CASE WHEN HOTEL = 'X' AND Booked_Status = 'Booked' THEN 1 END).
• Oracle 12c supports this syntax natively—no need for pivot functions that might be harder to read.

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