如何用单条SQL语句实现分组与Pivot透视需求？

Hey there! Let's tackle this SQL pivot problem you've been stuck on—sounds like you've been grinding on it for a while, so let's break it down step by step.

First, let's recap what you need: you want to turn your date-dispersed results into a pivot table where each row is a center, each column is a month's count, and you have a total row for all centers combined. The UNION approach was giving you scattered rows, so let's use PIVOT (and a few extra tricks) to get the structure you want.

Step 1: Get Monthly Aggregates First #

Before we pivot, we need to calculate the count of records per center per month for 2018. This CTE (Common Table Expression) will handle that cleanly:

WITH monthly_counts AS (
    SELECT 
        center,
        TO_CHAR(achiev_dat, 'MM') AS month_num, -- Use numeric month for consistent pivoting
        COUNT(*) AS record_count
    FROM CONV_HC.CARE_PLANS
    WHERE 
        center IN (902, 913, 923, 931, 961)
        AND EXTRACT(YEAR FROM achiev_dat) = 2018 -- Filter to 2018 only
    GROUP BY center, TO_CHAR(achiev_dat, 'MM')
)

This gives us a tidy set of rows with each center, its 2-digit month, and the number of records for that month.

Step 2: Pivot to Turn Months into Columns #

Now we'll use the PIVOT function to rotate those month rows into columns. We'll also add NVL to show 0 instead of NULL for months with no data, and use GROUP BY ROLLUP to generate the total row:

SELECT 
    NVL(TO_CHAR(center), 'Total') AS center,
    NVL("01", 0) AS january,
    NVL("02", 0) AS february,
    NVL("03", 0) AS march,
    NVL("04", 0) AS april,
    NVL("05", 0) AS may,
    NVL("06", 0) AS june,
    NVL("07", 0) AS july,
    NVL("08", 0) AS august,
    NVL("09", 0) AS september,
    NVL("10", 0) AS october,
    NVL("11", 0) AS november,
    NVL("12", 0) AS december,
    -- Calculate total per center (or overall total for the Total row)
    SUM(
        NVL("01",0) + NVL("02",0) + NVL("03",0) + NVL("04",0) + 
        NVL("05",0) + NVL("06",0) + NVL("07",0) + NVL("08",0) + 
        NVL("09",0) + NVL("10",0) + NVL("11",0) + NVL("12",0)
    ) AS total_yearly
FROM monthly_counts
PIVOT (
    SUM(record_count) -- Aggregate the count for each month
    FOR month_num IN (
        '01' AS "01", '02' AS "02", '03' AS "03",
        '04' AS "04", '05' AS "05", '06' AS "06",
        '07' AS "07", '08' AS "08", '09' AS "09",
        '10' AS "10", '11' AS "11", '12' AS "12"
    )
)
GROUP BY ROLLUP(center) -- Adds the total row for all centers
ORDER BY 
    -- Push the Total row to the bottom of the results
    CASE WHEN center IS NULL THEN 1 ELSE 0 END,
    center;

Why Your CASE Statement Might Have Failed #

If you tried using CASE without proper aggregation, you might have ended up with more rows than expected. For example, if you didn't group by center and the month-derived value, you wouldn't get the consolidated counts per month per center. The PIVOT function handles this aggregation and rotation in one step, which is cleaner than writing 12 separate CASE clauses.

Quick Notes #

• If you're using a database other than Oracle (like MySQL or SQL Server), the date functions and pivot syntax might vary slightly. For example, MySQL uses DATE_FORMAT(achiev_dat, '%m') instead of TO_CHAR, and relies on conditional aggregation with CASE since it doesn't have a native PIVOT function.
• The ROLLUP clause is what generates the "Total" row—it aggregates all the center rows into one summary row automatically.

Hope this gets you the exact output you need!

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