1.--行列转换原表:姓名科目成绩张三语文80张三数学90张三物理85李四语文85李四物理82李四英语90李四政治70王五英语90转换后的表：姓名数学物理英语语文政治李四082908570王五009000张三90850800实例:createtablecj--创建表cj(IDIntIDENTITY(1,1)notnull,--创建列ID,并且每次新增一条记录就会加1NameVarchar(50),SubjectVarchar(50),ResultInt,primarykey(ID)--定义ID为表cj的主键);--Truncatetablecj--Select*fromcjInsertintocjSelect'张三','语文',80unionallSelect'张三','数学',90unionallSelect'张三','物理',85unionallSelect'李四','语文',85unionallSelect'李四','物理',82unionallSelect'李四','英语',90unionallSelect'李四','政治',70unionallSelect'王五','英语',90--行列转换Declare@sqlvarchar(8000)Set@sql='SelectNameas姓名'Select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultelse0end)['+Subject+']'from(selectdistinctSubjectfromcj)ascj--把所有唯一的科目的名称都列举出来Select@sql=@sql+'fromcjgroupbyname'Exec(@sql)2.行列转换--合并原表:班级学号111213212231转换后的表:班级学号11,2,321,231实例:CreatetableClassNo--创建表ClassNo(IDIntIDENTITY(1,1)notnull,--创建列ID,并且每次新增一条记录就会加1ClassVarchar(50),--班级列NumberVarchar(50),--学号列PrimaryKey(ID)--定义ID为表ClassNo的主键);--TruncateTableClassNo--Select*fromClassNoInsertIntoClassNoSelect1,1UnionallSelect1,2UnionallSelect1,3UnionallSelect2,1UnionallSelect2,2UnionallSelect3,1创建一个合并的函数--DropFunctionKFReturnCreateFunctionKFReturn(@ClassVarchar(50))ReturnsVarchar(8000)asBeginDeclare@strVarchar(8000)Set@str=''Select@str=@str+cast(NumberasVarchar(50))+','fromClassNoWhereClass=@ClassSet@str=SubString(@str,1,len(@str)-1)Return(@str)End--调用自定义函数得到结果SelectDistinctClass,dbo.KFReturn(Class)FromClassNo3:列转行--DropTableColumnToRowCreatetableColumnToRow(IDIntIDENTITY(1,1)notnull,--创建列ID,并且每次新增一条记录就会加1aint,bint,cint,dint,eint,fint,gint,hint,PrimaryKey(ID)--定义ID为表ColumnToRow的主键);--TruncateTableColumnToRow--Select*fromColumnToRowInsertIntoColumnToRowSelect15,9,1,0,1,2,4,2UnionallSelect22,34,44,5,6,7,8,7UnionallSelect33,44,55,66,77,88,99,12Declare@sqlVarchar(8000)Set@sql=''Select@sql=@sql+rtrim(name)+'fromColumnToRowunionallSelect'fromSysColumnsWhereid=obj