Hey there! Let's figure out why your PIVOT output isn't matching what you expect, especially since you only want 2018 data. Looking at the sample output you shared, I spot a few common issues that could be causing this:

1. You aren't filtering for 2018 data upfront #

If your source table includes records from multiple years, failing to filter for 2018 before running the PIVOT can lead to incorrect values or extra rows. Always narrow down your data to the target year in a subquery or CTE first.

2. The extra id column is creating duplicate rows #

Your output includes an id column, which is likely a unique identifier for individual records. When you include this in the columns you group by (the SELECT clause before PIVOT), SQL Server will create a separate row for every unique id + ControllerNo combination. That's why you're seeing multiple rows for the same ControllerNo—you should remove this id column unless you explicitly need to group by it (which doesn't seem to be the case here).

3. Your aggregation function might not match your needs #

Double-check that the function you're using in the PIVOT (like SUM or COUNT) aligns with what you're trying to calculate. For example, if you're counting occurrences per month, use COUNT(*), but if you're summing a numeric value, stick with SUM(YourValueColumn).

Corrected PIVOT Example #

Assuming your source table has columns ControllerNo, RecordDate, and a value column you want to aggregate (like Reading), here's an adjusted query:

SELECT 
    ControllerNo,
    ISNULL(jan, 0) AS jan,
    ISNULL(feb, 0) AS feb,
    ISNULL(mar, 0) AS mar,
    ISNULL(apr, 0) AS apr,
    ISNULL(may, 0) AS may,
    ISNULL(jun, 0) AS jun,
    ISNULL(jul, 0) AS jul,
    ISNULL(aug, 0) AS aug,
    ISNULL(sep, 0) AS sep,
    ISNULL(oct, 0) AS oct,
    ISNULL(nov, 0) AS nov,
    ISNULL(dec, 0) AS dec
FROM (
    -- Filter 2018 data and extract month names
    SELECT 
        ControllerNo,
        -- Convert date to 3-letter month name to match your pivot columns
        LEFT(DATENAME(MONTH, RecordDate), 3) AS MonthName,
        Reading -- Replace with your actual column to aggregate
    FROM YourSourceTable
    WHERE YEAR(RecordDate) = 2018
) AS SourceData
PIVOT (
    -- Use the right aggregation function for your use case
    SUM(Reading) -- Swap with COUNT(*) if you're counting records instead
    FOR MonthName IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
) AS PivotResult;

Quick Notes: #

• ISNULL ensures any months with no data show 0 instead of NULL, matching your sample output.
• If your source data uses numeric months (1-12) instead of names, adjust the subquery to use MONTH(RecordDate) and pivot on [1], [2], ..., [12] instead, then alias them to jan, feb, etc.
• Make sure you're only including ControllerNo and the necessary month/value columns in the subquery—no extra columns like id that break the grouping.

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