MS SQL Server中PIVOT是否适合行转列并保留全部考试尝试记录？

你说得对，单纯用PIVOT确实不是解决这个问题的最佳选择——因为PIVOT的核心逻辑是聚合，默认会把同一分组下的多条记录合并成一条，这就会丢失你想要保留的多次尝试记录，还会导致学生姓名重复的问题。下面我给你详细拆解解决方案，帮你实现行转列同时保留所有尝试的日期和成绩：

一、先搞清楚：为什么PIVOT不直接适用？ #

PIVOT要求指定分组列和聚合函数（比如MAX/MIN），当同一学生同一测验有多次尝试时，聚合函数只会返回其中一条记录，没法保留所有尝试的细节。所以必须先给每个尝试加一个唯一标识，让PIVOT能区分开不同的尝试，这是解决问题的关键。

二、解决方案：先标记尝试序号，再拼接+转列 #

步骤1：给每个测验的尝试生成唯一序号 #

用ROW_NUMBER()函数，按学生ID、测验ID分区，按尝试次数排序，给每个尝试分配一个序号，这样同一学生同一测验的多次尝试就有了唯一的标识，同时我们可以提前把attemptdate和attemptgrade拼接成一个字段：

WITH QuizAttemptsWithSeq AS (
    SELECT 
        mdl_user.firstname + ' ' + mdl_user.lastname AS studentname,
        mdl_quiz.name AS quizname,
        -- 拼接完成时间和成绩，格式为"YYYY-MM-DD HH:MI:SS (成绩)"
        CONVERT(VARCHAR(20), mdl_quiz_attempts.timefinish, 120) + ' (' + CAST(mdl_quiz_attempts.sumgrades AS VARCHAR(10)) + ')' AS AttemptDetails,
        -- 生成同一测验下的尝试序号（比如第1次、第2次尝试）
        ROW_NUMBER() OVER (PARTITION BY mdl_user.id, mdl_quiz.id ORDER BY mdl_quiz_attempts.attempt) AS QuizAttemptSeq
    FROM mdl_quiz_attempts
    JOIN mdl_quiz ON mdl_quiz.id = mdl_quiz_attempts.quiz
    JOIN mdl_user ON mdl_user.id = mdl_quiz_attempts.userid
)
SELECT * FROM QuizAttemptsWithSeq;

步骤2：使用动态PIVOT实现行转列 #

因为测验数量和每个测验的尝试次数可能不固定，用动态SQL生成PIVOT的列会更灵活，不会因为新增测验或尝试次数变化而修改代码：

DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 自动生成所有需要转列的列名，格式为 [测验名称_尝试N]
SELECT @PivotColumns = STRING_AGG(QUOTENAME(quizname + '_Attempt' + CAST(QuizAttemptSeq AS VARCHAR(2))), ', ')
FROM (
    SELECT DISTINCT quizname, QuizAttemptSeq
    FROM QuizAttemptsWithSeq
) AS UniqueCols;

-- 构建动态PIVOT语句
SET @DynamicSQL = N'
WITH QuizAttemptsWithSeq AS (
    SELECT 
        mdl_user.firstname + '' '' + mdl_user.lastname AS studentname,
        mdl_quiz.name AS quizname,
        CONVERT(VARCHAR(20), mdl_quiz_attempts.timefinish, 120) + '' ('' + CAST(mdl_quiz_attempts.sumgrades AS VARCHAR(10)) + '')'' AS AttemptDetails,
        ROW_NUMBER() OVER (PARTITION BY mdl_user.id, mdl_quiz.id ORDER BY mdl_quiz_attempts.attempt) AS QuizAttemptSeq
    FROM mdl_quiz_attempts
    JOIN mdl_quiz ON mdl_quiz.id = mdl_quiz_attempts.quiz
    JOIN mdl_user ON mdl_user.id = mdl_quiz_attempts.userid
)
SELECT studentname, ' + @PivotColumns + '
FROM QuizAttemptsWithSeq
PIVOT (
    MAX(AttemptDetails)
    FOR quizname + ''_Attempt'' + CAST(QuizAttemptSeq AS VARCHAR(2)) IN (' + @PivotColumns + ')
) AS PivotResult
ORDER BY studentname;
';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

额外说明：静态PIVOT（适合测验数量固定的场景） #

如果你的业务中测验数量固定（比如最多2门），也可以直接用静态PIVOT，省去动态SQL的复杂度：

WITH QuizAttemptsWithSeq AS (
    SELECT 
        mdl_user.firstname + ' ' + mdl_user.lastname AS studentname,
        mdl_quiz.name AS quizname,
        CONVERT(VARCHAR(20), mdl_quiz_attempts.timefinish, 120) + ' (' + CAST(mdl_quiz_attempts.sumgrades AS VARCHAR(10)) + ')' AS AttemptDetails,
        ROW_NUMBER() OVER (PARTITION BY mdl_user.id, mdl_quiz.id ORDER BY mdl_quiz_attempts.attempt) AS QuizAttemptSeq
    FROM mdl_quiz_attempts
    JOIN mdl_quiz ON mdl_quiz.id = mdl_quiz_attempts.quiz
    JOIN mdl_user ON mdl_user.id = mdl_quiz_attempts.userid
)
SELECT 
    studentname,
    [数学测验_Attempt1], [数学测验_Attempt2],
    [英语测验_Attempt1], [英语测验_Attempt2]
FROM QuizAttemptsWithSeq
PIVOT (
    MAX(AttemptDetails)
    FOR quizname + '_Attempt' + CAST(QuizAttemptSeq AS VARCHAR(2)) IN (
        [数学测验_Attempt1], [数学测验_Attempt2],
        [英语测验_Attempt1], [英语测验_Attempt2]
    )
) AS PivotResult
ORDER BY studentname;

这里用MAX()作为聚合函数只是为了满足PIVOT的语法要求，因为我们已经通过ROW_NUMBER()给每个尝试生成了唯一的列标识，每个分组下只有一条记录，所以不会丢失任何尝试数据。

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