求一个奇怪的SQL视图语句。个人认为相当复杂。

[iwebet]

现有两个表，结构如下。
表1
ID Type
1 |1|3|
2 |2|
3 |4|5|6|

表2
ID Layer Name
1 0 A
2 0 B
3 1 C
4 0 D
5 4 E
6 4 F

请问如何建立一个视图返回如下结果
ID Name
1 A,C,
2 B,
3 D,E,F,

[iwebet]

有人知道么？

[My65sky]

这种查询用函数应该可以解决。

[子陌红尘]

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
go

create view v1 as
select ID,Name=dbo.f_str(Type) from t1
go

select * 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