SQL Server 2016实现带计数求和及空值显示的选课数据透视

SQL Server 2016 没有原生动态透视功能，需要通过动态SQL拼接实现需求，以下是可直接替换你原有查询逻辑的完整代码，完全兼容Express版本：

• 代码逻辑自动适配你筛选出的所有课程，不需要手动写死课程列
• 自动生成首行课程学分展示行
• 自动计算每行末尾的总学分、选课门数汇总
• 未选课程单元格默认返回空值，符合你的要求

SET NOCOUNT ON;
-- 1. 落地筛选后的基础选课数据，替换下方注释部分为你原有的多表JOIN查询
IF OBJECT_ID('tempdb..#StudentCourse') IS NOT NULL DROP TABLE #StudentCourse;
SELECT 
    StudentId,
    FirstName,
    CourseCode,
    CourseTitle,
    Credit,
    CourseCode + CourseTitle AS PivotColName
INTO #StudentCourse
FROM 
    /* 替换为你原有的多表关联查询SQL
    保留原有筛选条件：SemesterId=63 AND 选课状态字段 IN ('Approved','Registered') AND FacultyId=2
    */
;

-- 2. 动态拼接透视列、首行学分行（兼容SQL Server 2016，无STRING_AGG依赖）
DECLARE @PivotCols NVARCHAR(MAX) = '',
        @CreditRowCols NVARCHAR(MAX) = '',
        @ExecSQL NVARCHAR(MAX) = '';

-- 拼接透视列名
SELECT @PivotCols = STUFF((
    SELECT ',' + QUOTENAME(PivotColName)
    FROM (SELECT DISTINCT PivotColName, CourseCode FROM #StudentCourse) t
    ORDER BY CourseCode
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'');

-- 拼接首行学分展示的字段
SELECT @CreditRowCols = STUFF((
    SELECT ',' + CAST(MAX(Credit) AS VARCHAR(10)) + ' AS ' + QUOTENAME(PivotColName)
    FROM #StudentCourse
    GROUP BY PivotColName, CourseCode
    ORDER BY CourseCode
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'');

-- 3. 拼接最终查询语句
SET @ExecSQL = '
-- 首行：课程固定学分
SELECT
    CAST(0 AS BIGINT) AS StudentId,
    ''课程学分'' AS FirstName,
    ' + @CreditRowCols + ',
    CAST(NULL AS INT) AS 总注册学分,
    CAST(NULL AS INT) AS 注册课程总门数
UNION ALL
-- 学生选课透视主体
SELECT
    StudentId,
    FirstName,
    ' + @PivotCols + ',
    SUM(Credit) AS 总注册学分,
    COUNT(CourseCode) AS 注册课程总门数
FROM #StudentCourse
PIVOT(
    MAX(Credit)
    FOR PivotColName IN (' + @PivotCols + ')
) pvt
GROUP BY StudentId, FirstName, ' + @PivotCols + '
ORDER BY
    CASE WHEN StudentId = 0 THEN 0 ELSE 1 END,
    StudentId
';

-- 执行查询
EXEC sp_executesql @ExecSQL;

-- 清理临时表
DROP TABLE IF EXISTS #StudentCourse;

调整说明 #

• 如果需要修改课程列的排序规则，调整拼接@PivotCols和@CreditRowCols时的ORDER BY条件即可，比如按学分倒序就改为ORDER BY MAX(Credit) DESC
• 如果需要未选课程单元格显示为空字符串而非NULL，在透视列外层套ISNULL(字段名, '')即可，对应修改@PivotCols的拼接逻辑
• 代码中用QUOTENAME处理课程名、课程代码中的特殊字符（方括号、单引号等），不会出现语法错误
• 首行的汇总列默认留空，符合常规透视表展示逻辑

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