表1:
autoid a
1 [1,2,3]
2 [1,2]
3 [2,3]表2:
autoid a b
1 名称1 内容1
2 名称2 内容2
3 名称3 内容3
4 名称4 内容4
5 名称5 内容5希望由这两个表组成一个新的视图:
autoid a ming neirong
1 [1,2,3] [名称1,名称2,名称3] [内容1,内容2,内容3]
2 [1,2] [名称1,名称2] [内容1,内容2]
3 [2,3] [名称2,名称3] [内容2,内容3]
autoid a
1 [1,2,3]
2 [1,2]
3 [2,3]表2:
autoid a b
1 名称1 内容1
2 名称2 内容2
3 名称3 内容3
4 名称4 内容4
5 名称5 内容5希望由这两个表组成一个新的视图:
autoid a ming neirong
1 [1,2,3] [名称1,名称2,名称3] [内容1,内容2,内容3]
2 [1,2] [名称1,名称2] [内容1,内容2]
3 [2,3] [名称2,名称3] [内容2,内容3]
insert into t1 select 1,'[1,2,3]'
insert into t1 select 2,'[1,2]'
insert into t1 select 3,'[2,3]'create table t2(autoid int,a varchar(10),b varchar(10))
insert into t2 select 1,'名称1','内容1'
insert into t2 select 2,'名称2','内容2'
insert into t2 select 3,'名称3','内容3'
insert into t2 select 4,'名称4','内容4'
insert into t2 select 5,'名称5','内容5'
go
create function f_str(@id varchar(10),@type int)
returns varchar(100)
as
begin
declare @ret varchar(100)
set @ret=''
select @ret=@ret+','+(case @type when 1 then a else b end)
from t2 where charindex(','+rtrim(autoid)+',',','+substring(@id,2,len(@id)-2)+',')>0
set @ret='['+stuff(@ret,1,1,'')+']'
return @ret
end
gocreate view v1 as
select *,dbo.f_str(a,1) as ming,dbo.f_str(a,2) as neirong from t1
goselect * from v1
go
drop view v1
drop function f_str
drop table t1,t2
go
-------------------------------------------------------------------------------------
用户定义函数罢了，不是动态SQL。
insert A select 1, '[1,2,3]'
union all select 2, '[1,2]'
union all select 3, '[2,3]'create table B(autoid int, a varchar(10), b varchar(10))
insert B select 1, '名称1', '内容1'
union all select 2, '名称2', '内容2'
union all select 3, '名称3', '内容3'
union all select 4, '名称4', '内容4'
union all select 5, '名称5', '内容5'
create function dbo.fun(@a varchar(100), @col varchar(100))
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+(case @col when 'a' then a else b end)+',' from B where charindex(rtrim(autoid), @a)>0 select @re=left(@re, len(@re)-1), @re='['+@re, @re=@re+']' return @re
endselect A.*, ming=dbo.fun(a, 'a'), neirong=dbo.fun(a, 'b') from A
--result
autoid a ming neirong
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 [1,2,3] [名称1,名称2,名称3] [内容1,内容2,内容3]
2 [1,2] [名称1,名称2] [内容1,内容2]
3 [2,3] [名称2,名称3] [内容2,内容3](3 row(s) affected)
insert into t1 select 1,'[1,2,3]'
insert into t1 select 2,'[1,2]'
insert into t1 select 3,'[2,3]'create table t2(autoid int,a varchar(10),b varchar(10))
insert into t2 select 1,'名称1','内容1'
insert into t2 select 2,'名称2','内容2'
insert into t2 select 3,'名称3','内容3'
insert into t2 select 4,'名称4','内容4'
insert into t2 select 5,'名称5','内容5'
goSELECT
A.autoid, A.a,
ming = N'['
+ REPLACE(STUFF(B.re.value('(/r/a)[1]', 'nvarchar(max)'), 1, 1, N''), N' ,', N',')
+ N']',
neirong = N'['
+ REPLACE(STUFF(B.re.value('(/r/b)[1]', 'nvarchar(max)'), 1, 1, N''), N' ,', N',')
+ N']'
FROM(
SELECT
*,
Axml = CONVERT(xml, '<r><c>'
+ REPLACE(SUBSTRING(a, 2, LEN(a) - 2), ',', '</c><c>')
+ '</c></r>')
FROM t1
)A
OUTER APPLY(
SELECT re = (
SELECT a, b
FROM t2
WHERE autoid IN(
SELECT aid = T.c.value('(.)[1]', 'int')
FROM A.Axml.nodes('/r/c') T(c))
FOR XML AUTO, TYPE
).query('<r>
<a>{for $i in /t2/@a return(concat(",", string($i)))}</a>
<b>{for $i in /t2/@b return(concat(",", string($i)))}</b>
</r>')
)B
GODROP TABLE t1, t2-- 结果
autoid a ming neirong
1 [1,2,3] [名称1,名称2,名称3] [内容1,内容2,内容3]
2 [1,2] [名称1,名称2] [内容1,内容2]
3 [2,3] [名称2,名称3] [内容2,内容3]