在PostgreSQL中将VARCHAR类型答案行数据按USER_ID转为多列视图

方案1：通用CASE WHEN聚合实现（无需额外扩展） #

思路：先给每个用户的答案生成序号，再按用户分组聚合，按序号提取对应答案作为列，适配你最多20条答案的需求。

CREATE VIEW user_answer_pivot AS
SELECT
  user_id,
  MAX(CASE WHEN rn = 1 THEN answer END) AS answer1,
  MAX(CASE WHEN rn = 2 THEN answer END) AS answer2,
  MAX(CASE WHEN rn = 3 THEN answer END) AS answer3,
  -- 按相同规则继续补充到rn=20即可
  MAX(CASE WHEN rn = 20 THEN answer END) AS answer20
FROM (
  SELECT
    user_id,
    answer,
    -- 如需按答题时间排序，请将ORDER BY answer替换为ORDER BY 你的答题时间字段
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY answer) AS rn
  FROM user_answers -- 替换为你的原表实际名称
) t
GROUP BY user_id
ORDER BY user_id;

方案2：使用crosstab函数简化实现（需启用tablefunc扩展） #

如果要减少重复的CASE WHEN代码，可以用PostgreSQL内置的交叉表函数实现，首先启用扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

再创建视图：

CREATE VIEW user_answer_pivot AS
SELECT *
FROM crosstab(
  'SELECT user_id, answer, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY answer) AS rn FROM user_answers ORDER BY 1,3',
  'SELECT generate_series(1,20)' -- 对应最多20个答案的序号范围
) AS ct (
  user_id INT,
  answer1 TEXT,
  answer2 TEXT,
  answer3 TEXT,
  -- 按相同规则继续补充到answer20即可
  answer20 TEXT
);

注意：视图要求列结构固定，如果后续单用户答案数量超过20需要手动补充列定义；如果需要完全动态的列输出，建议使用PL/pgSQL存储过程动态生成查询语句。

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