請教：關於遊標的使用

[了缘]

我這裡有一組數據：
name type china_sign no
Qualim B N 10001
中化 B N 10001
方樂新 B N 10001
光美 B N 10001
同源 B Y 10001
金世恒邦 B Y 10001
南俊 B N 10001

要實現的結果為：
B N 10001 南俊，光美，方樂新,中化,Qualim
B Y 10001 金世恒邦,同源
用遊標不知道怎麼來實現，請大家指教，謝謝！

[愉快的登山者]

1,建自定义函数
create function getstr(@id Nchar(4000))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(name)+N',' from table1
where type+china_sign=@id
if @str<>N''
set @str=left(@str,len(@str)-1)
return @str
end

GO

2,
select type, china_sign, no, dbo.getstr(type+china_sign) as names from table1 group by type, china_sign, no

[TaoGeGe]

說清楚點

[pengdali]

1,建自定义函数
create function getstr(@id char(1))
returns varchar(8000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+rtrim(name)+',' from table1 where type+china_sign=@id
select @str=left(@str,len(@str)-1) where @str<>''
return @str
end

GO

2,
select type, china_sign, no,dbo.getstr(china_sign) names from table1 group by type,china_sign,no

[97866]

Declare @name varChar(500),@type varChar(1),@C_S varChar(1),
@No varChar(8),@Swap char(1)
Create table #tmp01(type varChar(1),china_sign varChar(1),no varChar(8),
Name varChar(500))
Select @type='',@C_S='',@No='',@Name='',@swap=''
Declare Cu_A Cursor For Select Name,Type,China_Sing,No From 表
Open Cu_A
Fetch Cu_A Into @type,@C_S,@No,@Name
while @@FETCH_STATUS=1
begin
if @C_S<>@swap
begin
Set @Swap=@C_S
Insert into #tmp01 values(@type,@C_S,@No,@Name)
end
else
Update #tmp01 Set Name=Name+','+@Name where type=@type and no=@no
Fetch Cu_A Into @type,@C_S,@No,@Name
end
Close Cu_A
Deallocate Cu_A

[97866]

后面加一句：
Select * from #tmp01

[愉快的登山者]

create table @t (type char,china_sign char, no, char(5), names varchar(1000))
DECLARE cur1 CURSOR
FOR select type, china_sign, no, [name] from table1 order by type, china_sign
OPEN cur1
declare @type char
declare @china_sign char
declare @no char(5)
declare @name varchar(100)
declare @ltype char
declare @lchian_sign char
set @ltype = ''
set @lchian_sign = ''
FETCH NEXT FROM cur1 into @type, @china_sign, @no, @name
While @@FETCH_STATUS = 0
begin
if @type = @ltype and @china_sign = @lchina_sign
begin
update #t set names = names + ',' + @name where type = @type and china_sign = @china_sign
end
else
begin
insert #t
select @type, @china_sign, @no, @name
set @ltype = @type
set @lchian_sign = @chian_sign
end
FETCH NEXT FROM cur1 into @type, @china_sign, @no, @name
End
CLOSE cur1
DEALLOCATE cur1
select * from #t order by type, china_sign
drop table #t

[了缘]

謝謝大家的支持和幫助.

[了缘]

謝謝TaoGeGe(涛哥哥) 的大力幫助
謝謝大家

[了缘]

謝謝TaoGeGe(涛哥哥) 的大力幫助
謝謝大家

[愉快的登山者]

为什么给TaoGeGe(涛哥哥)高分？
请楼主说明！