CREATE FUNCTION getV(@ID int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @sql varchar(100)
SELECT @sql=isnull(@sql+',','')+value FROM B WHERE ID=@ID ORDER BY Name
RETURN @sql
end
go
select distinct a.ID,dbo.getV(a.ID) from A Left Join B on A.id=B.id
RETURNS varchar(100)
AS
BEGIN
DECLARE @sql varchar(100)
SELECT @sql=isnull(@sql+',','')+value FROM B WHERE ID=@ID ORDER BY Name
RETURN @sql
end
go
select distinct a.ID,dbo.getV(a.ID) from A Left Join B on A.id=B.id
create function f_getstr( @parentId int ) returns varchar(200)
as
begin
declare @s varchar(200)
set @s=''
select @s=@s+','+name from b
where ParentID= @parentId
return stuff(@s,1,1,'')
end
go
select a.id,isnull(b.name,'') from a left join
(
select ParentID,dbo.f_getstr(ParentID) name
from b
group by ParentID
) b
on a.id=b.ParentID