如何编写SQL查询将每位用户的答案以独立列展示？

现有数据表 #

User Table #

Question Table #

Answers Table #

期望结果（注：原示例答案存在数据偏差，以下为匹配实际数据的正确结果） #

解决方案 #

这本质是**行转列（Pivot）**需求，将用户的答案从行数据转换为以用户名为列名的结构，以下分两种场景给出实现方式：

1. 固定展示指定用户（通用SQL写法） #

如果只需展示已知的1@gmail.com和2@gmail.com，用条件聚合即可，所有数据库都支持：

SELECT 
    q.Questionname,
    -- 匹配用户1的答案，无则返回NULL（可加COALESCE替换为默认值，比如'未回答'）
    MAX(CASE WHEN u.UserName = '1@gmail.com' THEN a.Answer END) AS `1@gmail.com`,
    MAX(CASE WHEN u.UserName = '2@gmail.com' THEN a.Answer END) AS `2@gmail.com`
FROM Question q
LEFT JOIN Answers a ON q.QuestionID = a.QuestionId
LEFT JOIN User u ON a.LoginId = u.UserId
GROUP BY q.QuestionID, q.Questionname -- 必须加QuestionID，避免同名问题被错误合并
ORDER BY q.QuestionID;

2. 动态生成用户列（适配用户不固定场景） #

若需要根据User表自动生成所有用户列，不同数据库写法略有差异：

SQL Server 写法

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 自动生成列名字符串
SELECT @cols = STRING_AGG(QUOTENAME(UserName), ', ') FROM User;

-- 拼接并执行动态SQL
SET @query = N'
SELECT Questionname, ' + @cols + '
FROM (
    SELECT q.Questionname, u.UserName, a.Answer
    FROM Question q
    LEFT JOIN Answers a ON q.QuestionID = a.QuestionId
    LEFT JOIN User u ON a.LoginId = u.UserId
) AS src
PIVOT (
    MAX(Answer) FOR UserName IN (' + @cols + ')
) AS pvt
ORDER BY QuestionID;';

EXEC sp_executesql @query;

MySQL 写法

-- 生成条件聚合的列片段
SET @cols = (SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN UserName = ''', UserName, ''' THEN Answer END) AS `', UserName, '`')) FROM User);

-- 拼接并执行动态SQL
SET @query = CONCAT('
SELECT q.Questionname, ', @cols, '
FROM Question q
LEFT JOIN Answers a ON q.QuestionID = a.QuestionId
LEFT JOIN User u ON a.LoginId = u.UserId
GROUP BY q.QuestionID, q.Questionname
ORDER BY q.QuestionID;');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 写法（需先安装tablefunc扩展）

-- 启用行转列扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    -- 源数据查询
    'SELECT q.Questionname, u.UserName, a.Answer
     FROM Question q
     LEFT JOIN Answers a ON q.QuestionID = a.QuestionId
     LEFT JOIN User u ON a.LoginId = u.UserId
     ORDER BY q.QuestionID, u.UserName;',
    -- 列名查询
    'SELECT DISTINCT UserName FROM User ORDER BY UserName;'
) AS ct (
    Questionname TEXT,
    "1@gmail.com" TEXT,
    "2@gmail.com" TEXT,
    "3@gmail.com" TEXT
);

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