SQL Server：针对同一Year值对多字段（Units、平均值等）执行Pivot

Got it, let's break this down! You're totally right that most basic Pivot examples only focus on a single measure (like Units) against the Year field, but we can solve this by combining UNPIVOT + PIVOT to get your desired table structure.

First, Let's Define the Example Tables #

Let's say your source table looks like this (with a grouping column like Category, Year as the column to pivot, and Units/AVGs as your metrics):

And your target table should have metrics (Units/AVGs) as rows, with Year values as columns:

Static SQL Solution (Known Year Values) #

If you know all the Year values upfront, use this combined UNPIVOT + PIVOT approach:

SELECT
    Category,
    Metric,
    [2020],
    [2021]
FROM (
    -- Step 1: UNPIVOT to turn metric columns into rows
    SELECT
        Category,
        Year,
        Metric,
        Value
    FROM YourSourceTable
    UNPIVOT (
        Value FOR Metric IN (Units, AVGs)
    ) AS UnpivotedMetrics
) AS PivotReadyData
-- Step 2: PIVOT to turn Year values into columns
PIVOT (
    MAX(Value) FOR Year IN ([2020], [2021])
) AS FinalPivot
ORDER BY Category, Metric;

Dynamic SQL Solution (Auto-Handle New Years) #

If your Year values change over time (e.g., new years get added annually), use dynamic SQL to automatically include all existing Year values:

DECLARE @YearColumns NVARCHAR(MAX), @DynamicSQL NVARCHAR(MAX);

-- Grab all unique Year values and format them as quoted columns
SELECT @YearColumns = STRING_AGG(QUOTENAME(Year), ', ')
FROM (SELECT DISTINCT Year FROM YourSourceTable) AS UniqueYears;

-- Build the full dynamic SQL query
SET @DynamicSQL = N'
SELECT
    Category,
    Metric,
    ' + @YearColumns + N'
FROM (
    SELECT
        Category,
        Year,
        Metric,
        Value
    FROM YourSourceTable
    UNPIVOT (
        Value FOR Metric IN (Units, AVGs)
    ) AS UnpivotedMetrics
) AS PivotReadyData
PIVOT (
    MAX(Value) FOR Year IN (' + @YearColumns + N')
) AS FinalPivot
ORDER BY Category, Metric;
';

-- Execute the dynamic query
EXEC sp_executesql @DynamicSQL;

How This Works #

1. UNPIVOT: Takes your multiple metric columns (Units, AVGs) and converts them into rows, creating two new columns: Metric (storing "Units" or "AVGs") and Value (storing the corresponding number). This gives us a flat dataset where each row is a single metric-year combination.
2. PIVOT: Takes the Year values from the flat dataset and turns them into columns, using MAX(Value) to aggregate the metric values for each Category-Metric-Year combination (since each combination is unique, MAX just returns the single value).

Quick Notes #

• If your metrics have different data types (e.g., Units is INT, AVGs is DECIMAL), UNPIVOT will automatically convert them to a compatible type (usually the more flexible one, like DECIMAL).
• If you have NULL values in your source data, MAX(Value) will ignore them—you can wrap it in ISNULL(MAX(Value), 0) or similar if you want to replace NULLs with a default.

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