经典行转列例子

/*******************************************************************************
*课程表: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

如图