Postgres：列转行需求，Group By与crosstab查询均无效求助

Hey there! I’ve helped tons of folks work through pivot problems, so let’s break down why your GROUP BY and crosstab attempts might be falling flat, and get you a working solution.

First: Let’s Fix the GROUP BY Approach #

A lot of folks mess up the GROUP BY pivot by forgetting to pair CASE statements with aggregate functions, or misdefining the grouping columns. Let’s use a common example to walk through this:

Suppose your raw table (let’s call it survey_responses) looks like this:

Common Mistake (What You Might Be Doing) #

SELECT 
  user_id,
  CASE WHEN question = 'Age' THEN response END AS age,
  CASE WHEN question = 'Gender' THEN response END AS gender
FROM survey_responses
GROUP BY user_id;

This fails because you’re not aggregating the CASE results—PostgreSQL doesn’t know which value to pick for each user.

Correct GROUP BY Pivot #

Wrap each CASE in an aggregate (like MAX() or MIN(), since each user has one response per question):

SELECT 
  user_id,
  MAX(CASE WHEN question = 'Age' THEN response END) AS age,
  MAX(CASE WHEN question = 'Gender' THEN response END) AS gender
FROM survey_responses
GROUP BY user_id
ORDER BY user_id;

This will give you the pivoted output you want:

Next: Fixing the crosstab Query #

If you’re using PostgreSQL’s crosstab function, the most common pitfalls are missing extensions, incorrect query structure, or bad ordering.

Step 1: Ensure the tablefunc Extension is Installed #

First, you need to enable the extension that provides crosstab:

CREATE EXTENSION IF NOT EXISTS tablefunc;

If you skip this, you’ll get a "function crosstab(unknown) does not exist" error.

Step 2: Correct crosstab Syntax #

crosstab requires two queries:

1. The first query selects the row identifier, category column, and value column (must be ordered by row identifier first, then category).
2. The second query defines the distinct categories that will become your new columns (must be ordered to match the output columns).

Common Mistake (Incorrect Ordering or Missing Columns) #

-- This fails because the first query isn't ordered properly
SELECT * FROM crosstab(
  'SELECT user_id, question, response FROM survey_responses',
  'SELECT DISTINCT question FROM survey_responses'
) AS ct(user_id int, age text, gender text);

Correct crosstab Query #

SELECT * FROM crosstab(
  -- First query: ordered by user_id, then question to ensure consistent mapping
  'SELECT user_id, question, response FROM survey_responses ORDER BY 1, 2',
  -- Second query: ordered to match the output column order
  'SELECT DISTINCT question FROM survey_responses ORDER BY 1'
) AS ct(user_id int, age text, gender text);

This will return the same pivoted table as the GROUP BY method.

If You’re Still Stuck... #

If these solutions don’t work, double-check:

• Your raw table has no duplicate row-identifier + category pairs (if it does, you’ll need to aggregate values first in the crosstab source query).
• For crosstab, the number of columns in the AS ct(...) clause matches the number of distinct categories plus the row identifier.
• All data types in the pivoted columns match the source data type (e.g., if response is mixed text/numbers, cast it to text first).

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