多次对同一列应用PIVOT运算符的实现问题求助

Hey there! Let's figure out why your nested pivot query isn't giving you the expected results, and walk through some actionable fixes.

First, let's break down the core issue with your current code:
When you nest two PIVOT operations, the second pivot uses the result set from the first as its source. In your query, the first pivot keeps the real_field column (which is just an alias for field) alongside the pivoted string columns ([1] to [4]). When you run the second pivot on real_field, it groups by all non-pivoted columns from the first pivot—meaning record, [1], [2], [3], [4]—instead of just grouping by record. This leads to duplicated rows or missing values because it's not consolidating all data for a single record into one row.

Better Alternative: Use Conditional Aggregation (Simpler & More Predictable) #

Instead of nested pivots, conditional aggregation with CASE WHEN is almost always easier to debug and control for multi-type pivots like this. Here's how to rewrite your query to get the expected output:

SELECT
    record,
    -- Pivot string fields (1-4)
    MAX(CASE WHEN field = 1 THEN string_value END) AS [1],
    MAX(CASE WHEN field = 2 THEN string_value END) AS [2],
    MAX(CASE WHEN field = 3 THEN string_value END) AS [3],
    MAX(CASE WHEN field = 4 THEN string_value END) AS [4],
    -- Pivot real fields (5-6)
    MAX(CASE WHEN field = 5 THEN real_value END) AS [5],
    MAX(CASE WHEN field = 6 THEN real_value END) AS [6]
FROM my_table
GROUP BY record;

Why This Works:

• We group directly by record, ensuring all data for a single record is consolidated into one row.
• Each CASE WHEN statement targets exactly the field and value type we need, pulling the correct value for each pivoted column.
• No nested subqueries or pivots to complicate grouping logic.

If You Still Want to Use Nested Pivots (Fixed Version) #

If you prefer sticking with PIVOT syntax, you need to ensure the first pivot only keeps the columns you need for grouping and the second pivot. Here's the corrected version:

SELECT
    record,
    [1], [2], [3], [4],
    [5], [6]
FROM (
    -- First, clean up the source data to separate value types clearly
    SELECT
        record,
        field,
        string_value,
        real_value
    FROM my_table
) AS src
-- First pivot for string fields (1-4)
PIVOT (
    MAX(string_value) FOR field IN ([1], [2], [3], [4])
) AS string_pivot
-- Second pivot for real fields (5-6)
PIVOT (
    MAX(real_value) FOR field IN ([5], [6])
) AS real_pivot;

The key fix here is removing the redundant field AS real_field and field AS string_field aliases in the source subquery—this ensures the second pivot only groups by record and the already pivoted string columns, correctly consolidating the real values into the same row.

内容的提问来源于stack exchange，提问作者Eduardo Sánche