create function getcolordesc(@id as varchar(10))
returns varchar(100)
as
begin
declare @r varchar(100)
set @r=''
select @r=','+@r+b.status_chinese from table1 a join table2 b on a.status_english=b.status_id where a.id=@id
set @r=stuff(@r,1,1,'')
return(@r)
end
goselect id,dbo.getcolordesc(id) from table1 group by id
returns varchar(100)
as
begin
declare @r varchar(100)
set @r=''
select @r=','+@r+b.status_chinese from table1 a join table2 b on a.status_english=b.status_id where a.id=@id
set @r=stuff(@r,1,1,'')
return(@r)
end
goselect id,dbo.getcolordesc(id) from table1 group by id
select a.id,a=cast('' as varchar(8000)),b.[status-chinese]
into #t from talbe1 a,table2 b
where a.[status-id]=b.[status-id]
order by a.id,b.b.[status-id]declare @id varchar(10),@s varchar(8000)
update #t set @s=case @id when id then @s+'/'+[status-chinese] else [status-chinese] end
,a=@s,@id=id
select id,[status-chinese]=max(a)
from #t
group by id
但,我的table1在不断增大,而要得结果去不断生成临时表,有很麻烦。
zjcxc(邹建)有更好的方法吗??
比如存储过程,之类的解决方法。