假设有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
------------------------------------------------------------------------------------------------------------------------------------
解决思路:是先把字符串按逗号拆了，再根据tags表修改，最后再合并
if object_id('tempdb..#') is not null drop table #if object_id('tempdb..#t') is not null drop table #tif object_id('tempdb..#tb') is not null drop table #tb--按逗号分割select top 2000 id=identity(int,1,1) into # from syscolumns a,syscolumns bselect a.id,a.title,tagid=substring(a.tagid,b.id,charindex(',',a.tagid+',',b.id)-b.id) into #t from test a, # bwhere substring(','+a.tagid,b.id,1)=',' order by a.id,b.id--更新update #tset tagid=b.namefrom #t a,tags bwhere a.tagid=b.id--合并select *,cast(',' as varchar(8000)) as aa into #tb from #t order by id,tagid declare @id int,@name varchar(50) update #tb set @name=case @id when id then @name+','+tagid else tagid end,aa=@name,@id=id select id,title,max(aa) as tagid from #tb group by id,title
