资源描述：
《sqlserver生成数据透视表(交叉分析数据)》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、SqlServer生成数据透视表（交叉分析数据）收藏［表二课程信息表］StuID
2、CourselD
3、ScoreSESBSn20100190090301201002100090302201001950903022010029809030320100188米[表三成绩信息表]生成基础数据的代码CREATETABLEfdbo].[StuInfo](rStuID]fchar](6)COLLATEChinese_PRC_CI_ASNOTNULL,[StuName][varchar](20)COLLATEChinese_PRC_CI_ASNOTNULL)ON
4、IPRIMARY]GOCREATETABLE[dbo].[Courseinfo]([CourselD][char](6)COLLATEChinese_PRC_CI_ASNOTNULL,[CourseName][varchar](20)COLLATEChinese_PRC_CI_ASNOTNULL)ON[PRIMARY]GOCREATETABLE[dbo].[Scoreinfo]([StuID][charj(6)COLLATEChinese_PRC_CI_ASNOTNULL,rCourselDl[char](6)COLLATEChinese_PRC
5、_CI_ASNOTNULL,[Score][float]NULL)ON[PRIMARY]GOinsertstuInfo(StuID,StuName)values('090301；'张三')insertstuInfo(StuID,StuName)values('090302','李卩屮)insertstulnfo(StuID,StuName)values('090303','王五')insertCourseInfo(CourseID,CourseName)values('2010017数学')insertCourseInfo(CourseID,Co
6、urseName)values('201002'C语言)insertScoreInfo(StuID,CourselD,Score)values('090301','201001',90)insertScoreInfo(StuID,CourseID,Score)values('090301','201002；100)insertScoreInfo(StuID,CourseID,Score)values('090302*,'20100195)insertScoreInfo(StuID,CourseID,Score)values('090302',
7、'201002*,98)insertScoreInfo(StuID,CourseID,Score)values('090303*,'20100188)生成数据透视表(1)单表Scoreinfodeckire@sqlnvarchar(500)set@sql=•SELECT[StuID],*select@sql=@sql+fsum(caseCourselDwhen,+quotename(CourseID,',")+'thenscoreelse0end)as'+quotename(CourseID)+VfiomScoreinfogroupbyCour
8、selDselect@sql=left(@sql,len(@sql)-1)select@sql=@sql+sum(score)as[sum]fromScoreinfogroupby[StuID]'select@sqlexec(@sql)执行效杲图:1StuID201001201002sum09030190.0100.0190.0209030295.098.0193.0309030388.00.088.0(2)双表Stulnfo,Scoreinfodeclare@sqlnvarchar(500)set@sql='SELECT(selectstuNa
9、mefromStulnfowhereStuInfo.stuID=scoreInfo.stuID),1select@sql=@sql+'sum(caseCourselDwhenl+quotename(CourseID/HI)+,thenscoreelse0end)as1+quotename(CourseID)+VfromScoreinfogroupbyCourselDselect@sql=left(@sqlJen(@sql)-1)select@sql=@sql+sum(score)as[sum]fromScoreinfogroupby[StuID
10、]1select@sqlexec(@sql)执行效果图：stuName201001201002sum张三90.0100.0190.0李四
