[MSSQL]视图如何关联多行数据到数组？问题详见需求案例。

Question

假设有2个表，需要组成一个视图，分别如下：表1: [Tags]ID      Name
------------------------------
1        Tag1
2        Tag2
3        Tag3
4        Tag4
5        Tag5
...表2：[Test]ID      Title      TagID
------------------------------
1       标题1        1,4
2       标题2       1,3,4
3       标题3       2,5
4       标题4       3,4
5       标题5       2,3,4,5
...如上例，如建立一个视图，并根据表[Test]的[TagID]字段的数组，关联表[Tags]的[Name]字段对应的行，得到如下视图：视图1：[View_Test]ID      Title      Tags
-----------------------------
1       标题1      Tag1,Tag4
2       标题2      Tag1,Tag3,Tag4
3       标题3      Tag2,Tag5
4       标题4      Tag3,Tag4
5       标题5      Tag2,Tag3,Tag4,Tag5
因为刚刚接触，什么都不懂，请给较完整的实例。谢谢

Answer 1

create table tags(ID int,      Name  varchar(10))
insert tags select 1, 'Tag1' 
union all select 2   ,'Tag2' 
union all select 3   ,'Tag3' 
union all select 4   ,'Tag4' 
union all select 5   ,'Tag5' 
go
create table a (ID int,      Title  varchar(20),    TagID  varchar(50))
insert a select 1       ,'标题1',      '1,4' 
union all select 2      ,'标题2',       '1,3,4' 
union all select 3       ,'标题3',      '2,5' 
union all select 4       ,'标题4',      '3,4' 
union all select 5       ,'标题5',      '2,3,4,5' 
go
create function getAll(@TagId varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+',','')+name from tags where charindex(','+ltrim(id)+',',','+@tagid+',')>0
return @sql
end
go
create view vw_getALL
as
select *,dbo.getALL(tagid) result from a 
go
select * from vw_getALL

Answer 2

--result
/*
ID          Title                TagID                          result                         
----------- -------------------- ------------------------------ ------------------------------ 
1           标题1                  1,4                            Tag1,Tag4
2           标题2                  1,3,4                          Tag1,Tag3,Tag4
3           标题3                  2,5                            Tag2,Tag5
4           标题4                  3,4                            Tag3,Tag4
5           标题5                  2,3,4,5                        Tag2,Tag3,Tag4,Tag5（所影响的行数为 5 行）
*/

Answer 3

create table tags(ID int,      Name  varchar(10)) 
insert tags select 1,  'Tag1 '  
union all select 2   , 'Tag2 '  
union all select 3   , 'Tag3 '  
union all select 4   , 'Tag4 '  
union all select 5   , 'Tag5 '  
go 
create table a (ID int,      Title  varchar(20),    TagID  varchar(50)) 
insert a select 1       , '标题1 ',       '1,4 '  
union all select 2      , '标题2 ',        '1,3,4 '  
union all select 3       , '标题3 ',       '2,5 '  
union all select 4       , '标题4 ',       '3,4 '  
union all select 5       , '标题5 ',       '2,3,4,5 '  
go -- 只限于 sql 2005 或者更高版本
create view v_test
as
WITH
DATA AS(
SELECT
ID,
TabID = CONVERT(xml, '<id>' + REPLACE(TagID, ',' , '</id><id>') + '</id>')
FROM a 
),
RE AS(
SELECT
A.ID, 
C.name
FROM DATA A
CROSS APPLY A.TabID.nodes('/id') T(c)
INNER JOIN tags C
ON T.C.value('.[1]', 'int') = C.ID
)
SELECT 
A.ID, A.Title,
names = STUFF(B.names, 1, 1, N'')
FROM a
CROSS APPLY(
SELECT names = (
SELECT 
',' + name
FROM RE
WHERE ID = A.ID
FOR XML PATH(''), ROOT('r'), TYPE
).value('/r[1]', 'nvarchar(max)')
)B
goselect * from v_test
go 
drop table tags, a
drop view v_test

Answer 4

-- 结果：
ID          Title                names
----------- -------------------- -----------------------------
1           标题1                  Tag1 ,Tag4 
2           标题2                  Tag1 ,Tag3 ,Tag4 
3           标题3                  Tag2 ,Tag5 
4           标题4                  Tag3 ,Tag4 
5           标题5                  Tag2 ,Tag3 ,Tag4 ,Tag5 (5 行受影响)

Answer 5

邹建老师，非常感谢您的解答，我在SQL 2005下执行了一下，没有任何问题。
但是我现在有一个关于使用的问题，上例中，视图创建后，我点击修改提示“不支持 表值方法 SQL 构造或语句。”，如果我日后需要修改视图怎么办呢？把创建视图的SQL语句保存为文件，删除现有视图后重新执行吗？