create table t1(ID int,Type varchar(20)) insert into t1 select 1,'|1|3|' insert into t1 select 2,'|2|' insert into t1 select 3,'|4|5|6|'create table t2(ID int,Layer int,Name varchar(10)) insert into t2 select 1,0,'A' insert into t2 select 2,0,'B' insert into t2 select 3,1,'C' insert into t2 select 4,0,'D' insert into t2 select 5,4,'E' insert into t2 select 6,4,'F' go create function f_str(@type varchar(20)) returns varchar(100) as begin select @type=replace(@type,'|'+rtrim(ID)+'|','|'+Name+'|') from t2 set @type=replace(stuff(@type,1,1,''),'|',',') return @type end gocreate view v1 as select ID,Name=dbo.f_str(Type) from t1 goselect * from v1 go/* ID Name ----------- -------- 1 A,C, 2 B, 3 D,E,F, */ drop view v1 drop function f_str drop table t1,t2 go
insert into t1 select 1,'|1|3|'
insert into t1 select 2,'|2|'
insert into t1 select 3,'|4|5|6|'create table t2(ID int,Layer int,Name varchar(10))
insert into t2 select 1,0,'A'
insert into t2 select 2,0,'B'
insert into t2 select 3,1,'C'
insert into t2 select 4,0,'D'
insert into t2 select 5,4,'E'
insert into t2 select 6,4,'F'
go
create function f_str(@type varchar(20))
returns varchar(100)
as
begin
select @type=replace(@type,'|'+rtrim(ID)+'|','|'+Name+'|') from t2
set @type=replace(stuff(@type,1,1,''),'|',',')
return @type
end
gocreate view v1 as
select ID,Name=dbo.f_str(Type) from t1
goselect * from v1
go/*
ID Name
----------- --------
1 A,C,
2 B,
3 D,E,F,
*/
drop view v1
drop function f_str
drop table t1,t2
go