如何用存储过程实现？？

Question

动态SQL,行列转换,你查下这方面的

Answer 1

我想要的结果是：
         TeacherID     TeacherName   Student1  Student2  Student3  Student4 
        123456         王强           王小强     李刚        王小波     李大刚,李刚强,李小刚

Answer 2

我用的就是 sql server 2005

Answer 3

我要的结果中不是简单的将行转换成列，我想要的是有顺序的。
排名第一的学生放在Studet1中，排名第二的学生放在Student2中，但排名第二的学生在子表中是不一定是第二条记录

Answer 4

create table Student(StudentID int,   TeacherID int,    StudentName nvarchar(20) ,    [Sequence] nvarchar(10))
insert  Student select 123456001,           123456,         '王小强',             'NO1' 
insert  Student select 123456002,           123456,         '王小波',             'NO3' 
insert  Student select 123456003,           123456,         '李刚',                 'NO2' 
insert  Student select 123456004,           123456,         '李大刚',                 'NO4' 
insert  Student select 123456005,           123456,         '李小刚',                 'NO6' 
insert  Student select 123456006,           123456,         '李刚强',                 'NO5' godeclare @s nvarchar(4000),@i int
select 
@i=max(con)
from 
(select count(1) as con from Student group by TeacherID)T
set @s=''while @i>0
select @s='[StudentName'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then StudentName else '''' end),'
+@s,@i=@i-1
set @s=left(@s,len(@s)-1)exec('select TeacherID,'+@s+
'from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT group by TeacherID')TeacherID   StudentName1         StudentName2         StudentName3         StudentName4         StudentName5         StudentName6         
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
123456      王小强                  李刚                   王小波                  李大刚                  李刚强                  李小刚

Answer 5

select TeacherID,[StudentName1]=max(case when con=1 then StudentName else '' end),[StudentName2]=max(case when con=2 then StudentName else '' end),[StudentName3]=max(case when con=3 then StudentName else '' end),[StudentName4]=max(case when con=4 then StudentName else '' end),[StudentName5]=max(case when con=5 then StudentName else '' end),[StudentName6]=max(case when con=6 then StudentName else '' end)from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT group by TeacherID
动态生成的语句如下:

Answer 6

动态生成的语句如上..
复制到下面去了 Sequence有大小顺序时，可以这个实现

Answer 7

create table Teacher(TeacherID int,TeacherName varchar(10))
insert into teacher select 123456,'王强' 
create table Student(StudentID int,TeacherID int,StudentName varchar(10),[Sequence] varchar(4))
insert into student select 123456001,123456         ,'王小强','NO1' 
insert into student select 123456002,123456         ,'王小波','NO3' 
insert into student select 123456003,123456         ,'李刚','NO2' 
insert into student select 123456004,123456         ,'李大刚','NO4' 
insert into student select 123456005,123456         ,'李小刚','NO6' 
insert into student select 123456006,123456         ,'李刚强','NO5'
gocreate function f_str(@teacher int)
returns varchar(100)
as
begin
declare @s varchar(8000)
select @s = ''
select @s = @s + ','+studentname from student where cast(substring([Sequence],3,10)  as int) > 3
return @send 
godeclare @sql varchar(8000)
set @sql = 'select a.TeacherID,a.TeacherName 'select @sql = @sql 
+ ',[student'+ltrim(N)+']= max(case when [Sequence] = ''NO'+ltrim(N)+''' then b.StudentName else '''' end)'
from (select 1 as N union select 2 union select 3) aaexec ( @sql+' ,dbo.f_str(a.TeacherID) as student4 from teacher a
left join student b
on a.TeacherID = b.TeacherID
group by a.TeacherID,a.TeacherName')drop table student,teacher
drop function f_str/*TeacherID   TeacherName student1   student2   student3   student4                                                                                             
----------- ----------- ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 
123456      王强          王小强        李刚         王小波        ,李大刚,李小刚,李刚强
*/

Answer 8

create table Teacher(TeacherID int,TeacherName varchar(10))
insert into teacher select 123456,'王强' 
create table Student(StudentID int,TeacherID int,StudentName varchar(10),[Sequence] varchar(4))
insert into student select 123456001,123456         ,'王小强','NO1' 
insert into student select 123456002,123456         ,'王小波','NO3' 
insert into student select 123456003,123456         ,'李刚','NO2' 
insert into student select 123456004,123456         ,'李大刚','NO4' 
insert into student select 123456005,123456         ,'李小刚','NO6' 
insert into student select 123456006,123456         ,'李刚强','NO5'
gocreate function f_str(@teacher int)
returns varchar(100)
as
begin
declare @s varchar(8000)
select @s = ''
select @s = @s + ','+studentname from student where cast(substring([Sequence],3,10)  as int) > 3
set @s = stuff(@s,1,1,'')
return @send 
godeclare @sql varchar(8000)
set @sql = 'select a.TeacherID,a.TeacherName 'select @sql = @sql 
+ ',[student'+ltrim(N)+']= max(case when [Sequence] = ''NO'+ltrim(N)+''' then b.StudentName else '''' end)'
from (select 1 as N union select 2 union select 3) aaexec ( @sql+' ,dbo.f_str(a.TeacherID) as student4 from teacher a
left join student b
on a.TeacherID = b.TeacherID
group by a.TeacherID,a.TeacherName')drop table student,teacher
drop function f_str/*TeacherID   TeacherName student1   student2   student3   student4                                                                                             
----------- ----------- ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 
123456      王强          王小强        李刚         王小波        李大刚,李小刚,李刚强
*/

Answer 9

create table Teacher(  TeacherID int,    TeacherName    nvarchar(20)  ) 
insert  Teacher select    123456,           '王强' 
go
create table Student(StudentID int,   TeacherID int,    StudentName nvarchar(20) ,    [Sequence] nvarchar(10))
insert  Student select 123456001,           123456,         '王小强',             'NO1' 
insert  Student select 123456002,           123456,         '王小波',             'NO3' 
insert  Student select 123456003,           123456,         '李刚',                 'NO2' 
insert  Student select 123456004,           123456,         '李大刚',                 'NO4' 
insert  Student select 123456005,           123456,         '李小刚',                 'NO6' 
insert  Student select 123456006,           123456,         '李刚强',                 'NO5' godeclare @s nvarchar(4000),@i int
select 
    @i=max(con)
from 
    (select count(1) as con from Student group by TeacherID)T
set @s=''while @i>0
select @s='[StudentName'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then StudentName else '''' end),'
    +@s,@i=@i-1
set @s=left(@s,len(@s)-1)exec('select t2.TeacherID,'+@s+
'from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT join Teacher t2 on tt.TeacherID=t2.TeacherID
group by t2.TeacherID,t2.TeacherName')TeacherID   StudentName1         StudentName2         StudentName3         StudentName4         StudentName5         StudentName6         
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
123456      王小强                  李刚                   王小波                  李大刚                  李刚强                  李小刚select t2.TeacherID,[StudentName1]=max(case when con=1 then StudentName else '' end),[StudentName2]=max(case when con=2 then StudentName else '' end),[StudentName3]=max(case when con=3 then StudentName else '' end),[StudentName4]=max(case when con=4 then StudentName else '' end),[StudentName5]=max(case when con=5 then StudentName else '' end),[StudentName6]=max(case when con=6 then StudentName else '' end)from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT join Teacher t2 on tt.TeacherID=t2.TeacherID
group by t2.TeacherID,t2.TeacherName

Answer 10

select
t2.TeacherID,
[StudentName1]=max(case when con=1 then StudentName else '' end),
[StudentName2]=max(case when con=2 then StudentName else '' end),
[StudentName3]=max(case when con=3 then StudentName else '' end),
[StudentName4]=max(case when con=4 then StudentName else '' end)+','+max(case when con=5 then StudentName else '' end)+','+max(case when con=6 then StudentName else '' end)from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT join Teacher t2 on tt.TeacherID=t2.TeacherID
group by t2.TeacherID,t2.TeacherName--这样显示?

Answer 11

/*TeacherID   TeacherName student1   student2   student3   student4                                                                                             
----------- ----------- ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 
123456      王强          王小强        李刚         王小波        李大刚,李小刚,李刚强
*/

Answer 12

create table Teacher(TeacherID varchar(20),TeacherName varchar(20))
insert Teacher select '123456','王强'
insert Teacher select '123457','王刚'create table Student(StudentID varchar(20),TeacherID varchar(20),StudentName varchar(20),Sequence varchar(20))
insert Student select '123456001','123456','王小强','NO1' 
union all select '123456002','123456','王小波','NO3' 
union all select '123456003','123456','李刚','NO2' 
union all select '123456004','123456','李大刚','NO4' 
union all select '123456005','123456','李小刚','NO6'
union all select '123456006','123456','李刚强','NO5' 
union all insert student select '123456007','123457','李强','NO1' create function getStudent(@Tid varchar(20))
returns varchar(200)
as
begin
declare @sql varchar(200)
select @sql=isnull(@sql+',','')+studentname from
(
select top 100 percent StudentName from Student 
where TeacherID=@Tid and cast(replace(Sequence,'NO','') as int)>3 
order by cast(replace(Sequence,'NO','') as int)
)aa
return @sql
endgo
select a.TeacherID ,a.TeacherName ,b.*
from Teacher a,
(select TeacherID,
Student1=max(case when Sequence='NO1' then StudentName else '' end),
Student2=max(case when Sequence='NO2' then StudentName else '' end),
Student3=max(case when Sequence='NO3' then StudentName else '' end),
Student4=isnull(dbo.getStudent(TeacherID),'') from Student group by TeacherID
)b
where a.TeacherID=b.TeacherID--result
/*TeacherID            TeacherName          TeacherID            Student1             Student2             Student3             Student4                                                                                                                                                                                                 
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
123456               王强                   123456               王小强                  李刚                   王小波                  李大刚,李刚强,李小刚
123457               王刚                   123457               李强                                                             （所影响的行数为 2 行）
*/

Answer 13

下午有事走开：
--前三条记录分开显示，最后一条记录显示为一行,举个例子给楼主,也可以实现三条记录一条这样的显示，楼主改一下字符的生成规则就行了
create table Teacher(  TeacherID int,    TeacherName    nvarchar(20)  ) 
insert  Teacher select    123456,           '王强' 
go
create table Student(StudentID int,   TeacherID int,    StudentName nvarchar(20) ,    [Sequence] nvarchar(10))
insert  Student select 123456001,           123456,         '王小强',             'NO1' 
insert  Student select 123456002,           123456,         '王小波',             'NO3' 
insert  Student select 123456003,           123456,         '李刚',                 'NO2' 
insert  Student select 123456004,           123456,         '李大刚',                 'NO4' 
insert  Student select 123456005,           123456,         '李小刚',                 'NO6' 
insert  Student select 123456006,           123456,         '李刚强',                 'NO5' godeclare @s nvarchar(4000),@i int,@j int
select 
    @i=max(con)
from 
    (select count(1) as con from Student group by TeacherID)T
select @j=1while @j!>@i
select @s=isnull(@s+',','')+case when @j<5 then '[StudentName'+rtrim(@j)+']=max(case when con='+rtrim(@j)+' then StudentName else '''' end)'
else '+'',''+max(case when con='+rtrim(@j)+' then StudentName else '''' end)' end,@j=@j+1
set @s =replace(@s,',+','+')
exec('select t2.TeacherID,'+@s+
'from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT join Teacher t2 on tt.TeacherID=t2.TeacherID
group by t2.TeacherID,t2.TeacherName')
TeacherID   StudentName1         StudentName2         StudentName3         StudentName4                                                   
----------- -------------------- -------------------- -------------------- -------------------------------------------------------------- 
123456      王小强                  李刚                   王小波                  李大刚,李刚强,李小刚