如何在SQL Server 2012+中将查询结果转换为客户所需格式？

我拥有以下表结构：

candidateExamGrades
-------------------
courseID
examID
examName
candidateID
examGrade

candidateCourseGrades
----------------------
courseID
candidateID
courseName
courseGrade

candidate
----------
candidateID
candidateLastName
candidateDOB

我的现有查询语句如下：

SELECT
    c.candidateID, c.candidateLastName, 
    ce.examName, 
    cg.courseName, cg.courseGrade, 
    ce.examGrade
FROM 
    candidate c
INNER JOIN 
    candidateExamGrades ce ON c.candidateID = ce.candidateID
INNER JOIN 
    candidateCourseGrades cg ON c.candidateID = cg.candidateID

当前查询结果：

canidateID  lastName    examName                    courseName      courseGrade     examGrade
______________________________________________________________________________________________

991         Smith       Electrical Systems          Electrical           S          90.1
991         Smith       Power Inverters             Electrical           S          87.4
991         Smith       Secondary Power Sources     Electrical           H          89.9
991         Smith       Machine Safety              Safety               U          72.3
991         Smith       Emergency Procedures        Safety               U          68.3

但客户需要的数据格式如下：

canidateID  lastName    gradeBlock                      grade       
_______________________________________________________________________________________________________________

991         Smith       Electrical Systems Exam         90.1            
991         Smith       Power Inverters Exam            87.4            
991         Smith       Secondary Power Sources Exam    89.9            
991         Smith       Electrical Course Grade         S           
991         Smith       Machine Safety Exam             72.3
991         Smith       Emergency Procedures Exam       68.3
991         Smith       Safety Course Grade             U

即需要将考试、课程名称与对应成绩合并展示。请问在SQL Server 2012及以上版本中能否实现该需求？

可以实现，核心思路是用UNION ALL将考试成绩记录和课程成绩记录合并成同一维度的行数据，而非原查询的列关联方式。具体SQL语句如下：

SELECT
    c.candidateID,
    c.candidateLastName,
    CONCAT(ce.examName, ' Exam') AS gradeBlock,
    CAST(ce.examGrade AS VARCHAR(50)) AS grade
FROM candidate c
INNER JOIN candidateExamGrades ce ON c.candidateID = ce.candidateID

UNION ALL

SELECT
    c.candidateID,
    c.candidateLastName,
    CONCAT(cg.courseName, ' Course Grade') AS gradeBlock,
    cg.courseGrade AS grade
FROM candidate c
INNER JOIN candidateCourseGrades cg ON c.candidateID = cg.candidateID

关键说明： #

1. 考试成绩处理：将examName拼接固定后缀" Exam"作为gradeBlock，同时把数值型的examGrade转为字符串，保证与课程成绩的字段类型匹配（UNION ALL要求合并的列类型一致）。
2. 课程成绩处理：将courseName拼接固定后缀" Course Grade"作为gradeBlock，直接取courseGrade作为成绩列。
3. 结果合并：UNION ALL会保留所有原始记录，不会自动去重（若需去重可替换为UNION，但此场景无需）。

如果需要按考生ID和成绩项排序，可在语句末尾添加排序逻辑：

SELECT
    c.candidateID,
    c.candidateLastName,
    CONCAT(ce.examName, ' Exam') AS gradeBlock,
    CAST(ce.examGrade AS VARCHAR(50)) AS grade
FROM candidate c
INNER JOIN candidateExamGrades ce ON c.candidateID = ce.candidateID

UNION ALL

SELECT
    c.candidateID,
    c.candidateLastName,
    CONCAT(cg.courseName, ' Course Grade') AS gradeBlock,
    cg.courseGrade AS grade
FROM candidate c
INNER JOIN candidateCourseGrades cg ON c.candidateID = cg.candidateID

ORDER BY candidateID, gradeBlock;

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