计数和总和并显示空单元格的枢轴结果

Question

I have the following SQL:

    SELECT t6.StudentId,
       t6.FirstName,
       t2.Code,
       t2.Title,
       t2.Credit,
       t2.SemesterId
FROM
(
    SELECT t1.*,
           t7.Code,
           t7.Title,
           t7.Credit
    FROM StudentCourses t1
        JOIN CourseOfferings t7
            on t7.Id = t1.CourseOfferingId
    WHERE t1.SemesterId = 63
          AND t1.Status IN ( 'Approved', 'Registered')
) AS t2
    JOIN Students t6
        on t6.StudentId = t2.StudentId
    JOIN StudentCurriculum t3
        on t3.StudentId = t2.StudentId
    JOIN Curriculums t4
        on t4.Id = t3.CurriculumId
    JOIN StudyPrograms t5
        on t5.Id = t4.StudyProgramId
WHERE t5.FacultyId = 2

that gives me the following result:

StudentId	FirstName	Code	Title	Credit	SemesterId
7505	Toranong	MKTG3204	Marketing Channels Management and Distribution	3.0	63
7505	Toranong	GENL1111	Health and Wellness	3.0	63
7505	Toranong	MNGT2206	Managing Human Capital	3.0	63
7505	Toranong	MKTG3206	Global Marketing	3.0	63
7505	Toranong	MKTG3205	Integrated Marketing Communication	3.0	63
7505	Toranong	GENL1100	Seminar in Character Dev	0.0	63
7505	Toranong	BUAD3305	English for Career	3.0	63
7507	Kannika	GENL1100	Seminar in Character Dev	0.0	63
7507	Kannika	ACCT3213	Public Sector Accounting	3.0	63
7507	Kannika	ACCT3206	Advanced Accounting II	3.0	63
7507	Kannika	ACCT3209	Internal Audit and Risk Managemen	3.0	63
7507	Kannika	ACCT3211	Cost Management	3.0	63
7507	Kannika	BUAD3305	English for Career	3.0	63
7510	Siriporn	ACCT3211	Cost Management	3.0	63
7510	Siriporn	BUAD3305	English for Career	3.0	63
7510	Siriporn	ACCT3206	Advanced Accounting II	3.0	63
7510	Siriporn	ACCT3213	Public Sector Accounting	3.0	63
7510	Siriporn	GENL1100	Seminar in Character Dev	0.0	63
7510	Siriporn	ACCT3209	Internal Audit and Risk Managemen	3.0	63
7512	Aphisit	MKTG3206	Global Marketing	3.0	63
7512	Aphisit	MKTG3205	Integrated Marketing Communication	3.0	63
7512	Aphisit	MNGT2206	Managing Human Capital	3.0	63
7512	Aphisit	GENL1111	Health and Wellness	3.0	63
7512	Aphisit	GENL1100	Seminar in Character Dev	0.0	63
7512	Aphisit	BUAD3305	English for Career	3.0	63
7512	Aphisit	MKTG3204	Marketing Channels Management and Distribution	3.0	63
7517	Pitchaya	GENL1100	Seminar in Character Dev	0.0	63
7517	Pitchaya	ACCT3206	Advanced Accounting II	3.0	63
7517	Pitchaya	ACCT3209	内部审计和风险管理员	3.0	63

[省略了简短

]想要将结果转移到以下格式中：

单击此处查看表

ID	名称	通信	BUAD355国际业务	BUAD456全球业务	ENGL237亚洲文学	Engl243人际交流	ENGL251公共演讲	ENGL254应用语音和词语	ENGL258重要阅读和思考	ENGL259专业写作	Enkl341社交媒体媒体通信	Engl341社交媒体通信ENGL352英语Grammar Grammar Grammar	Engl392介绍Resory＆Amp＆Amp＆Amp＆Amp＆Amp＆Amp＆Amp;著作	Hist212 Hist的介绍。 文明	人类计算机互动	网络和数据通信数学	MKTG232	原则	钢琴	语音	PSYC115	入门	入门
		课	MATH081	Musc121	中级	西方	IT272	课程	代数	MUSC111	市场	营销	IT282	2	3	3	3	3	1	1	3
7148	Chanraiya				3			3		3												9	3
7149	Anattanon				3	3	3										3					12	4
7150	Rotcharat		3							3			3	2				3				14	5
7151	Sosipho	3								3		3	3					3				15	5
7152	Porapat			3							3		3					3			3	15	5
7153	Zhou				3	3			3	3					3 3	3 3						18	6
7154	SU				3	3	3	3	3 3	3												18	6
7155	Liu				3	3 3		3	3 3	3									1	1		17	7

第一行是课程学分，您可以在上图中看到它。 我正在SQL Server 2016 Express上编写SQL。