SQL 行转列 PIVOT 学习示例

2019-01-02 14:15:34来源:cnblogs.com作者:阿小星人点击

分享
CREATE TABLE [StudentScores]([UserName] NVARCHAR(20), --学生姓名[Subject] NVARCHAR(30), --科目[Score] FLOAT, --成绩)INSERT INTO [StudentScores] SELECT '张三', '语文', 80INSERT INTO [StudentScores] SELECT '张三', '数学', 90INSERT INTO [StudentScores] SELECT '张三', '英语', 70INSERT INTO [StudentScores] SELECT '张三', '生物', 85INSERT INTO [StudentScores] SELECT '李四', '语文', 80INSERT INTO [StudentScores] SELECT '李四', '数学', 92INSERT INTO [StudentScores] SELECT '李四', '英语', 76INSERT INTO [StudentScores] SELECT '李四', '生物', 88INSERT INTO [StudentScores] SELECT '码农', '语文', 60INSERT INTO [StudentScores] SELECT '码农', '数学', 82INSERT INTO [StudentScores] SELECT '码农', '英语', 96INSERT INTO [StudentScores] SELECT '码农', '生物', 78select * from [StudentScores]SELECT * FROM [StudentScores] /*数据源*/AS P PIVOT (SUM(Score/*行转列后 列的值*/) FOR p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)) AS T where username<>'李四'

相关文章

    无相关信息

微信扫一扫

第七城市微信公众平台