declare @id char(3),@attribute char(2)
declare cur cursor for
select*from B
open cur
FETCH NEXT FROM cur
into @id,@attribute
WHILE @@FETCH_STATUS = 0
BEGIN
update A
set A.attribute=isnull(A.attribute+',','')+@attribute
where A.id=@id
FETCH NEXT FROM cur
into @id,@attribute
END
CLOSE cur
DEALLOCATE cur
declare cur cursor for
select*from B
open cur
FETCH NEXT FROM cur
into @id,@attribute
WHILE @@FETCH_STATUS = 0
BEGIN
update A
set A.attribute=isnull(A.attribute+',','')+@attribute
where A.id=@id
FETCH NEXT FROM cur
into @id,@attribute
END
CLOSE cur
DEALLOCATE cur
returns varchar(1000)
as
begin
declare @b varchar(1000)
select @b = ''
select @b = 属性 + ',' + @b from b where 编号 = @a
return @b
end使用:
select 编号,dbo.f_a(编号) from a
Create Table A
(编号 Varchar(10),
属性 Varchar(50))Create Table B
(编号 Varchar(10),
属性 Varchar(10))
GO
--插入数据
Insert A Values('001',Null)
Insert A Values('002',Null)
Insert A Values('003',Null)Insert B Values('001','01')
Insert B Values('001','02')
Insert B Values('001','03')
Insert B Values('002','02')
Insert B Values('003','02')
Insert B Values('003','03')
GO
--建立函数
CREATE FUNCTION GetA(@ID Varchar(10))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SET @s=''
SELECT @s=@s+','+属性 FROM B WHERE 编号=@ID
RETURN(substring(@s,2,8000))
END
GO
--测试
Select 编号,属性=dbo.GetA(编号) from A
--删除测试环境
Drop table A,B
Drop FUNCTION GetA
--结果
/*
编号 属性
001 01,02,03
002 02
003 02,03
*/
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+type from B where id=@id
return(stuff(@s,1,1,''))
-- stuff('string',start,length,'string')刪除指定長度的字符并在指定的起始點插入另一組字符
end
go --调用函数
select id,dbo.f_catString(id) as type from B group by id
go --删除测试环境
Drop Table B