Question

sql server 2008存储与游标（把查询结果多行变为一行）

create table xs(id char(10) primary key,name char(10),score int,credit int,sex char(4),brithday datetime,remarks text,)
insert into xs values ('1','张三','99','30','1','1900-1-2','无')insert into xs values ('2','李萨','88','70','1','1993-3-1','无')insert into xs values ('3','王杰','100','55','2','1989-1-11','无')insert into xs values ('4','1杰','100','55','2','1989-1-11','无')insert into xs values ('5','2杰','100','55','2','1989-1-11','无')
存储：
create procedure xx_1@s intasselect name+credit from xs where score=@s
调用 exec xx_1 '100'
结果：

我想把结果改成：name+credit，name+credit，name+credit。
王杰 55,1杰 55,2杰 55

Answer 1

create procedure xx_1 (@s int)
as
begin
declare @name char(10),@credit int,@str varchar(4000)
select @str=''
declare xx cursor for select name,credit from xs where score=@s
open xx
fetch xx into @name,@credit
while @@fetch_status=0
begin
select @str=@str+rtrim(ltrim(@name))+space(1)+cast(@credit as varchar(10))+','
fetch xx into @name,@credit
end
close xx
deallocate xx
print left(@str,len(@str)-1)
end

go

exec xx_1 100