难题啊,查询的列数量不固定,这该怎么办啊?

Question

For example:
--测试数据
create table 基础表(姓名 nvarchar(10),课程名 nvarchar(20),成绩 decimal(10,1))
insert 基础表 select '王家喜','计算机基础',69.0
union  all   select '王家喜','邓小平理论',74.0
union  all   select '王家喜','英语（上）',86.0
union  all   select '王家喜','普通逻辑学',91.0
union  all   select '施春林','立法学教程',60.0
union  all   select '施春林','经管原理'  ,73.0
union  all   select '施春林','英语（上）',73.0
union  all   select '施春林','普通逻辑学',90.0
go--查询
declare @s varchar(8000),@i varchar(10)
select top 1 @s='',@i=count(*)
from 基础表 group by 姓名 order by count(*) desc
while @i>0
select @s=',[课程'+@i+']=max(case when id='+@i
+' then 课程名 else '''' end),[成绩'+@i
+']=max(case when id='+@i
+' then 成绩 end)'+@s
,@i=@i-1
exec('
select 姓名,课程名,成绩,id=0 into #t from 基础表 order by 姓名
declare @i int,@姓名 varchar(10)
update #t set @i=case when @姓名=姓名 then @i+1 else 1 end,id=@i,@姓名=姓名
select 姓名'+@s+' from #t group by 姓名')
go--删除测试
drop table 基础表/*--测试结果姓名    课程1        成绩1   课程2     成绩2   课程3     成绩3   课程4      成绩4  
-------- ----------- ------- --------- ------ --------- ------- ---------- -------
施春林   立法学教程  60.0    经管原理   73.0   英语（上） 73.0    普通逻辑学  90.0
王家喜   计算机基础  69.0    邓小平理论 74.0   英语（上） 86.0    普通逻辑学  91.0
--*/

Answer 1

对,行列转换,参考下面的动态用法.
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

Answer 2

用动态SQL来实现，主要适用于交叉表！

Answer 3

支持1楼，7楼！！
方法：
首先根据第二个条件抽出数据放到表1中
然后把编号转化为一个表的形式，放到表二中
最后，表1 cross join 表2就可得到搂主需要的结果附：编号字符串转化为表的函数：
--Re　:例 分割される数組 (1,2,3,4,5,6)
--                分割符号 (,)
--                   結果 | col  |
--                       |  1   |
--                       |  2   |
--                       |  3   |
--                       |  4   |
--                       |  5   |
--                       |  6   |CREATE FUNCTION WF_split(@StrArray VARCHAR(8000),@StrSeprate VARCHAR(10))
RETURNS @temp TABLE(col VARCHAR(100))
AS 
BEGIN
    DECLARE @i INT
    SET @StrArray=RTRIM(LTRIM(@StrArray))
    SET @i=CHARINDEX(@StrSeprate,@StrArray)
    WHILE @i>=1
        BEGIN
            INSERT @temp VALUES(LEFT(@StrArray,@i-1))
            SET @StrArray = SUBSTRING(@StrArray,@i+1,LEN(@StrArray)-@i)
            SET @i = CHARINDEX(@StrSeprate,@StrArray)
        END
    IF @StrArray<>'\'
    INSERT @temp VALUES(@StrArray)
    RETURN 
END

Answer 4

動態SQL,我之前寫了一個類似的,我的那個是品保系統的一個報表,因為每批的不良規則都不一樣.
我的思路是用兩次遊標.第一次用遊標來設置動態的字段.第二次是根據字段來添加數據進去.