t-sql经典行专列、列转行,分页及存储过程

《t-sql经典行专列、列转行,分页及存储过程》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、T-SQL经典编程说明：本实例是以SQLServer2005为运行环境的。准备工作：创建一个名为DB的数据库（CREATEDATABASEDB）。一、T-SQL行转列1、创建如下表CREATETABLE[Scores]([ID]INTIDENTITY(1,1),--自增标识[StuNo]INT,--学号[Subject]NVARCHAR(30),--科目[Score]FLOAT--成绩)GOINSERTINTO[Scores]SELECT100,'语文',80UNIONSELECT100,'数学',75UNIONSE

2、LECT100,'英语',70UNIONSELECT100,'生物',85UNIONSELECT101,'语文',80UNIONSELECT101,'数学',90UNIONSELECT101,'英语',70UNIONSELECT101,'生物',85CREATETABLE[Student]([ID]INTIDENTITY(100,1),--自增标识，学号[StuName]NVARCHAR(30),--姓名[Sex]NVARCHAR(30),--性别[Age]CHAR(2)--年龄)GOINSERTINTO[Stude

3、nt]SELECT'张三','男',80UNIONSELECT'李四','女',75两表的数据如下图：2、通过CASE…WHEN语句和GROUPBY…聚合函数来实现行转列SELECTStuNoAS'学号',MAX(CASESubjectWHEN'语文'THENScoreELSE0END)AS'语文',MAX(CASESubjectWHEN'数学'THENScoreELSE0END)AS'数学',MAX(CASESubjectWHEN'英语'THENScoreELSE0END)AS'英语',MAX(CASESubjec

4、tWHEN'生物'THENScoreELSE0END)AS'生物',SUM(Score)AS'总分',AVG(Score)AS'平均分'FROMdbo.[Scores]GROUPBYStuNoORDERBYStuNoASC结果如下图：3、通过JOIN…ON实现两表联接，显示出学生姓名SELECTMAX(StuNo)AS'学号',StuNameAS'姓名',MAX(CASESubjectWHEN'语文'THENScoreELSE0END)AS'语文',MAX(CASESubjectWHEN'数学'THENScoreEL

5、SE0END)AS'数学',MAX(CASESubjectWHEN'英语'THENScoreELSE0END)AS'英语',MAX(CASESubjectWHEN'生物'THENScoreELSE0END)AS'生物',SUM(Score)AS'总分',AVG(Score)AS'平均分'FROMdbo.[Scores]Ajoin[Student]Bon(A.StuNo=B.ID)GROUPBYStuNameORDERBYStuNameASC结果如下图：4、通过PIVOT实现行转列SELECTStuNoAS'学号',S

6、tuNameAS'姓名',AVG(语文)AS'语文',AVG(数学)AS'数学',AVG(英语)AS'英语',AVG(生物)AS'生物'FROM[Scores]PIVOT(AVG(Score)FORSubjectIN(语文,数学,英语,生物))ASNewScoresJOIN[Student]ON(NewScores.StuNo=Student.ID)GROUPBYNewScores.StuNo,StuNameORDERBYStuNameASC结果如下图：二、T-SQL列转行1、创建数据表并插入4条数据CREATETA

7、BLE[StudentScores]([ID]INTIDENTITY(1,1),--自增标识[StuNo]INT,--学号[Chinese]NVARCHAR(30),--语文[Mathematics]NVARCHAR(30),--数学[English]NVARCHAR(30),--英语[Biology]NVARCHAR(30)--生物)GOINSERTINTO[StudentScores]SELECT100,80,85,75,80UNIONSELECT101,90,80,70,75UNIONSELECT102,95,

8、90,80,70UNIONSELECT103,60,70,80,85数据如下图：2、通过UNIONALL…MAX实现列转行SELECTStuNo,'Chinese'ASSubject,MAX(Chinese)AS'Score'FROM[StudentScores]GROUPBY[StuNo]UNIONALLSELECTStuNo,'Mathem