如何用SQL的PIVOT无聚合转换2×2表？现有代码报错求排查

Let’s break down the issues in your code and fix them step by step:

Key Errors in Your Code #

• Missing Aggregate Function: The PIVOT clause requires an aggregate function (like SUM, MAX, MIN, or COUNT) to summarize the values you want to pivot. Your code uses number(123), which is invalid syntax—this isn’t a valid aggregate operation.
• Incorrect SELECT Column: After pivoting, the name column won’t exist in the resulting pivot_table because you’re pivoting on the name column itself. You shouldn’t include it in your final SELECT list.
• Unclear 2x2 Table Structure: Your current query only targets one name (Andy), which won’t produce a 2x2 table. You’ll need to include a second row dimension (like a group/category column) and a second name to get the desired layout.

Corrected Basic Pivot (Single Column) #

If you just want to pivot the number value for Andy into a column (assuming each name has one unique number), use this:

SELECT Andy
FROM (
    SELECT name, number
    FROM Customer
) t
PIVOT (
    MAX(number)  -- Use MAX since each name has one value; adjust to SUM/COUNT if needed
    FOR name IN (Andy)
) AS pivot_table;

Example for 2×2 Table #

Let’s assume your Customer table has an additional column (e.g., group_id) that creates the row dimension (values 1 and 2), and you want to pivot two names (Andy and Bob) as columns. Here’s how to get a 2x2 table:

Sample Customer Table Data #

Pivot Query for 2×2 Result #

SELECT group_id, Andy, Bob
FROM (
    SELECT group_id, name, number
    FROM Customer
) t
PIVOT (
    MAX(number)
    FOR name IN (Andy, Bob)
) AS pivot_table;

Resulting 2×2 Table #

Notes #

• Adjust the aggregate function (MAX, SUM, etc.) based on your data: use SUM if you need to add up numbers for a name, COUNT if you need to count entries, etc.
• If your number column has NULLs, the aggregate function will handle them appropriately (e.g., MAX ignores NULLs).

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