/*******************************************************************************
*课程表:Course
********************************************************************************/
CREATE TABLE #Course ( CourseName VARCHAR(10) );
INSERT INTO #Course
( CourseName )
VALUES ( '语文' ),
( '数学' ),
( '英语' )
SELECT *
FROM #Course
/*******************************************************************************
*学生成绩表:Score
********************************************************************************/
CREATE TABLE #Score
(
StudentName VARCHAR(10) ,
CourseName VARCHAR(10) ,
Score INT
);
INSERT INTO #Score
( StudentName, CourseName, Score )
VALUES ( '小红', '语文', 76 ),
( '小红', '数学', 85 ),
( '小红', '英语', 55 ),
( '小李', '语文', 90 ),
( '小李', '英语', 74 ),
( '小林', '语文', 87 ),
( '小林', '数学', 90 )
SELECT *
FROM #Score
/*******************************************************************************
*组织所有的课程
********************************************************************************/
DECLARE @CourseName VARCHAR(200) = ''
DECLARE @SelectSQL NVARCHAR(2000)
DECLARE @sql NVARCHAR(2000)
SELECT @CourseName = @CourseName + '[' + CourseName + '],'
FROM #Course
SET @CourseName = LEFT(@CourseName, LEN(@CourseName) - 1)
/*******************************************************************************
*行转列操作
********************************************************************************/
SET @SelectSQL = 'SELECT pvt.*
FROM
(
SELECT StudentName, CourseName, Score FROM #Score
) p
PIVOT( SUM(Score) For CourseName in ({0})) AS pvt'
SET @SelectSQL = REPLACE(@SelectSQL, '{0}', @CourseName)
EXEC sp_executesql @SelectSQL
/*总分*/
SET @sql = ' SELECT m.* , n.total
FROM
(
SELECT * FROM
(
SELECT * FROM #Score
) p
PIVOT (SUM(Score) FOR CourseName in ('+ @CourseName + ')) b
) m ,
(
SELECT StudentName,sum(Score) AS total from #Score
GROUP BY StudentName
)n
WHERE m.StudentName= n.StudentName'
EXEC sp_executesql @sql
/*******************************************************************************
*删除临时表
********************************************************************************/
DROP TABLE #Course
DROP TABLE #Score
如图
