请教高手，sql结果，多行转一行，并限制列数。

Question

表结构：
列:         ClassID                 StudentName
            1                         a1         
            1                         a2         
            2                         a1         
            3                         a2  
            2                         d1         
            4                         d1  
            3                         a3         
            1                         b2  希望输出结果： 不同 student分不同列，student最多2列
列:         ClassID                 Student1             Student2         Student3（不显示）
            1                         a1                   a2                b2（不显示）
            2                         a1                   d1                
            3                         a2                   a3              
            4                         d1                  （不显示）
找了好久，网上的例子都是把student按字符串放在一列。小弟在线等。
谢谢

Answer 1

/*
标题：普通行列转换(version 2.0)
说明：普通行列转换(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 课程
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.姓名')

Answer 2

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ClassID] int,[StudentName] varchar(2))
insert [TB]
select 1,'a1' union all
select 1,'a2' union all
select 2,'a1' union all
select 3,'a2' union all
select 2,'d1' union all
select 4,'d1' union all
select 3,'a3' union all
select 1,'b2'select * from [TB]SELECT classid,
[STU1] = CASE WHEN studentname = 'a1' THEN studentname ELSE '' END,
[STU2] = CASE WHEN studentname = 'a2' THEN studentname ELSE '' END
FROM dbo.TB
GROUP BY classid,studentname
SELECT classid,studentname AS STU1,STU2 = (SELECT TOP 1 studentname FROM tb WHERE A.classid = classid and tb.studentname <>A.studentname ORDER BY studentname asc)
FROM dbo.TB A
WHERE studentname IN (SELECT TOP 1 studentname FROM tb WHERE A.classid = classid ORDER BY studentname asc)/*
classid STU1 STU2
1 a1 a2
2 a1 d1
3 a2 a3
4 d1 NULL*/

Answer 3

SELECT classid,studentname AS STU1,STU2 = (SELECT TOP 1 studentname FROM tb WHERE A.classid = classid and tb.studentname <>A.studentname ORDER BY studentname asc)
FROM dbo.TB A
WHERE studentname IN (SELECT TOP 1 studentname FROM tb WHERE A.classid = classid ORDER BY studentname asc)光这个，中间乱写的也发上来了哈。 

Answer 4

--sql 2000
create table [TB]([ClassID] int,[StudentName] varchar(2))
insert [TB]
select 1,'a1' union all
select 1,'a2' union all
select 2,'a1' union all
select 3,'a2' union all
select 2,'d1' union all
select 4,'d1' union all
select 3,'a3' union all
select 1,'b2'
goselect ClassID , 
       max(case px when 1 then StudentName else '' end) StudentName1,
       max(case px when 2 then StudentName else '' end) StudentName2
from
(
  select * , px = (select count(1) from tb where ClassID = t.ClassID and StudentName < t.StudentName) + 1 from tb t
) m
group by ClassIDdrop table tb/*
ClassID     StudentName1 StudentName2 
----------- ------------ ------------ 
1           a1           a2
2           a1           d1
3           a2           a3
4           d1           （所影响的行数为 4 行）
*/
--sql 2005
select ClassID , 
       max(case px when 1 then StudentName else '' end) StudentName1,
       max(case px when 2 then StudentName else '' end) StudentName2
from
(
  select * , px = row_number() over(partition by ClassID order by StudentName) from tb t
) m
group by ClassID

Answer 5

既然是student最多2列，那为什么为classid为1的，却有三列a1,a2,b2
另外，如何确保classid为1时，输出的为a1,a2，而不是a2,b2呢
我觉得lz没有把题目说明白

Answer 6

表结构：
列: ClassID StudentName
1 a1 
1 a2 
2 a1 
3 a2 
2 d1 
4 d1 
3 a3 
1 b2 希望输出结果： 不同 student分不同列，student最多2列
列: ClassID Student1 Student2 Student3（不显示）
1 a1 a2 b2（不显示）
2 a1 d1 
3 a2 a3 
4 d1 （不显示）
找了好久，网上的例子都是把student按字符串放在一列。
做法:
declare @li_class integer
declare @li_cnt integer
create table #tmp(ClassID int,Student1 varchar(20),Student2 varchar(20))
declare cur cursor for 
select distinct ClassID from tt  --tt所有的ClassID
open cur
fetch next from cur into @li_class
while @@fetch_status=0
begin
  set @li_cnt=(select count(ClassID) from tt where ClassID=@li_class)
  if @li_cnt=2 
  begin
    --将两行合并成一行,放到临时表中#tmp
    insert into #tmp(ClassID,Student1,Student2) select top 1 a.ClassID,a.StudentName as Student1,b.StudentName as Student2 from tt a inner join tt b on a.ClassID=b.ClassID and a.StudentName<>b.StudentName and ClassID=@li_class
  end
  fetch next from cur into @li_class
end
select * from #tmp
--删除临时表#tmp
if object_id('tempdb..#tmp') is not null
begin
    drop table #tmp
end