行转列测试数据：

--测试数据if not object_id(N'Tempdb..#T') is null	drop table #TGoCreate table #T([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)Insert #Tselect N'李四',N'语文',60 union allselect N'李四',N'数学',70 union allselect N'李四',N'英语',80 union allselect N'张三',N'语文',90 union allselect N'张三',N'数学',80 union allselect N'张三',N'英语',70Go--测试数据结束

动态写法（加上了总分和平均分）：

DECLARE @sql VARCHAR(MAX)SET @sql = 'select Name'SELECT  @sql = @sql + ',max(case Subject when ''' + Subject        + ''' then Score else 0 end)[' + Subject + ']'FROM    ( SELECT DISTINCT                    Subject          FROM      #T        ) aSET @sql = @sql    + ',sum(Score) 总分,cast(avg(Score*1.0) as decimal(18,2)) 平均分 from #T group by Name'EXEC(@sql)

动态的也可以使用pivot：

DECLARE @sql VARCHAR(MAX)SELECT @sql=isnull(@sql+',','')+Subject FROM #T GROUP BY SubjectSET @sql='select m.* , n.总分, n.平均分 from(select * from (select * from #T) a pivot (max(Score) for Subject in ('+@sql+')) b) m ,(select Name,sum(Score)总分, cast(avg(Score*1.0) as decimal(18,2))平均分 from #T group by Name) nwhere m.Name= n.Name'exec(@sql)

列转行的测试数据：

--测试数据  if not object_id(N'Tt') is nulldrop table TtGoCreate table Tt([姓名] nvarchar(22),[语文] int,[数学] int,[英语] int)Insert Ttselect N'张三',60,70,80 union all select N'李四',90,80,70Go--测试数据结束

 动态写法：

DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from T'FROM syscolumnsWHERE Name!='姓名' AND ID=object_id('T')--表名tb，不包含列名为姓名的其他列ORDER BY colidEXEC(@sql+' order by 姓名')

 同样的动态写法也可以使用unpivot：

DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','')+quotename(Name)FROM syscolumnsWHERE ID=object_id('T')AND Name NOT IN('姓名')ORDER BY ColidSET @sql='select 姓名,[课程],[分数] from T unpivot ([分数] for [课程] in('+@sql+'))b'exec(@sql)