/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-16 12:01:51
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
    Mar 29 2009 10:27:29 
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([student] NVARCHAR(10),[语文] INT,[数学] INT,[英语] INT)
INSERT [tb]
SELECT 'student1',80,90,85 UNION ALL
SELECT 'student2',85,92,82
GO
--SELECT * FROM [tb]

-->SQL查询如下:
SELECT student,课程,分数
FROM tb
    UNPIVOT(分数 FOR 课程 IN(语文,数学,英语))b 
/*
student    课程    分数
student1    语文    80
student1    数学    90
student1    英语    85
student2    语文    85
student2    数学    92
student2    英语    82
*/

------解决方案--------------------
SQL code
IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([student] NVARCHAR(10),[语文] INT,[数学] INT,[英语] INT)
INSERT [tb]
SELECT 'student1',80,90,85 UNION ALL
SELECT 'student2',85,92,82
GO
--SELECT 



declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [student],[课程]='+quotename(Name,N'''')
             --isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[分数]='+quotename(Name)+' from tb'
from syscolumns where ID=object_id('tb') and Name not in('student')--排除不转换的列
order by Colid

print @s
exec(@s+N' order by [student]')
/*
student    课程    分数
student1    语文    80
student1    数学    90
student1    英语    85
student2    英语    82
student2    数学    92
student2    语文    85
*/
--
select [student],[课程]='语文',[Score]=[语文] from tb 
union all select [student],[课程]='数学',[Score]=[数学] 
from tb union all select [student],[课程]='英语',[Score]=[英语] from tb
  order by [student]