温故简单SQL行列转换
-- 创建表CREATE TABLE T_Score(ScoreId INT IDENTITY (1,1) NOT NULL,StuName NVARCHAR(50),Subject NVARCHAR(50),ScoreINT)-- 插入数据INSERT INTO T_Score(StuName,Subject,Score)SELECT '李四','英语',88 UNION ALLSELECT '李四','语文',99 UNION ALLSELECT '李四','化学',78 UNION ALLSELECT '李四','历史',82 UNION ALLSELECT '李四','物理',98 UNION ALLSELECT '王五','英语',89 UNION ALLSELECT '刘萍','英语',77 UNION ALLSELECT '刘萍','语文',68 UNION ALLSELECT '王六','英语',81 UNION ALLSELECT '马林','英语',84--行列转换（显示数据）DECLARE @SQL VARCHAR(8000)SET @SQL='SELECT StuName AS 姓名'SELECT @SQL=@SQL+',SUM(CASE Subject WHEN '''+Subject+''' THEN Score ELSE 0 END) ['+Subject+']'FROM (SELECT DISTINCT Subject FROM T_Score) AS ttSELECT @SQL=@SQL+' FROM T_Score GROUP BY StuName'EXEC(@SQL)
