求一个视图语句补全

领导让我建立视图，从左边的表中读取数据生成右边表的那个样子，
还有张关联表是
表名tcl_cms_relatecolumndoc
columnid
documentid
这两个字段

另一张表
表名tcl_cms_devicetype
id
devicetype
两个字段

SQL语句：

select ID,name Title,parentColumnId ParentID,ColLevel Level,
(select count(*) from tcl_cms_relatecolumndoc where columnid=a.id) as ContentNum,
colType Ctype,
(select top 1 name from TCL_CMS_VideoColumn where id=a.ParentColumnId)+'/'+name as PathTitle,
colOrder ClassOrder,ColHidden ClassHidden,
AppDeviceTypes,
EnName EnTitle,
(select top 1 Enname from TCL_CMS_VideoColumn where id=a.ParentColumnId)+'/'+Enname as EnPathTitle
from TCL_CMS_VideoColumn a

原表中AppDeviceTypes是一个id字符串，比如1,2,4,7,9 或者还有种是(all)
需要把非(all)的id串通过关联表查询替换成tcl_cms_devicetype表中的devicetype串，也是以逗号隔开。
这个语句有点复杂，我纠结了。请高手帮帮我。。

无非又是charindex..哎

脑袋疼 想不出来啊。。。

最好列出一些数据看看
如何更有效地在SQL Server论坛上提问
http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320.html?55074

我尝试把语句改了，里面对于ID的替换用了个function
select ID,name Title,parentColumnId ParentID,ColLevel Level,
(select count(*) from tcl_cms_relatecolumndoc where columnid=a.id) as ContentNum,
colType Ctype,
(select top 1 name from TCL_CMS_VideoColumn where id=a.ParentColumnId)+'/'+name as PathTitle,
colOrder ClassOrder,ColHidden ClassHidden,
CASE WHEN AppDeviceTypes <> '(all)' THEN 
dbo.f_devicetype(AppDeviceTypes)
ELSE '(all)' END AppDeviceTypes,
EnName EnTitle,
(select top 1 EnName from TCL_CMS_VideoColumn where id=a.ParentColumnId)+'/'+EnName as EnPathTitle
from TCL_CMS_VideoColumn a

function代码
Create function f_devicetype(@id varchar(1000))
returns varchar(5000)
as 
begin
declare @str varchar(5000)
set @str=''
select @str=@str+','+[devicetype] from tcl_cms_devicetype where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go

但是替换出来只有一个名称，而不是一串名称。

SQL code

我尝试把语句改了，里面对于ID的替换用了个function
select ID,name Title,parentColumnId ParentID,ColLevel Level,
(select count(*) from tcl_cms_relatecolumndoc where columnid=a.id) as ContentNum,
colType Ctype,
(select top 1 name from TCL_CMS_VideoColumn where id=a.ParentColumnId)+'/'+name as PathTitle,
colOrder ClassOrder,ColHidden ClassHidden,
CASE WHEN AppDeviceTypes <> '(all)' THEN  
dbo.f_devicetype(AppDeviceTypes)
ELSE '(all)' END AppDeviceTypes,
EnName EnTitle,
(select top 1 EnName from TCL_CMS_VideoColumn where id=a.ParentColumnId)+'/'+EnName as EnPathTitle
from TCL_CMS_VideoColumn a

function代码
Create function f_devicetype(@id varchar(1000))
returns varchar(5000)
as  
begin
declare @str varchar(5000)
set @str=''
select @str=@str+','+[devicetype] from tcl_cms_devicetype where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go

但是替换出来只有一个名称，而不是一串名称。

try:
SQL code

update a set appdevicetypes=replace(','+a.appdevicetypes+',',','+ltrim(b.id)+',',','+b.devicetype+',')
from tcl_cms_videocloumn a inner join tcl_cms_devicetype b on charindex(','+ltrim(b.id)+',',','+a.appdevicetypes+',')>0

update tcl_cms_videocloumn set appdevicetype=substring(appdevicetype,2,len(appdevicetype)-2)
where left(appdevicetype,1)=','

SQL code

create table tcl_cms_videocloumn(appdevicetypes varchar(2000))
insert into tcl_cms_videocloumn select '3,5,7'
insert into tcl_cms_videocloumn select '1,2,3,6,13'
--insert into tcl_cms_videocloumn select 'all'
create table tcl_cms_devicetype(id int,devicetype varchar(10))
insert into tcl_cms_devicetype select 1,'aaa'
insert into tcl_cms_devicetype select 2,'bb'
insert into tcl_cms_devicetype select 3,'cccc'
insert into tcl_cms_devicetype select 4,'dddd'
insert into tcl_cms_devicetype select 5,'ee'
insert into tcl_cms_devicetype select 6,'ffff'
insert into tcl_cms_devicetype select 7,'rr'
insert into tcl_cms_devicetype select 10,'xxxx'
insert into tcl_cms_devicetype select 13,'zzz'
go
update tcl_cms_videocloumn set appdevicetypes=','+appdevicetypes+',' where appdevicetypes<>'all'
while exists(select 1 from tcl_cms_videocloumn a inner join tcl_cms_devicetype b on charindex(','+ltrim(b.id)+',',a.appdevicetypes)>0)
update a set appdevicetypes=replace(a.appdevicetypes,','+ltrim(b.id)+',',','+b.devicetype+',')
from tcl_cms_videocloumn a inner join tcl_cms_devicetype b on charindex(','+ltrim(b.id)+',',a.appdevicetypes)>0
go
update tcl_cms_videocloumn set appdevicetypes=substring(appdevicetypes,2,len(appdevicetypes)-2)
where left(appdevicetypes,1)=','
select * from tcl_cms_videocloumn
/*
appdevicetypes
------------------------------------------------------------------
cccc,ee,rr
aaa,bb,cccc,ffff,zzz

(2 行受影响)

*/
go
drop table tcl_cms_videocloumn,tcl_cms_devicetype

SQL code

create table tcl_cms_videocloumn(appdevicetypes varchar(2000))
insert into tcl_cms_videocloumn select '3,5,7'
insert into tcl_cms_videocloumn select '1,2,3,6,13'
insert into tcl_cms_videocloumn select 'all'
create table tcl_cms_devicetype(id int,devicetype varchar(10))
insert into tcl_cms_devicetype select 1,'aaa'
insert into tcl_cms_devicetype select 2,'bb'
insert into tcl_cms_devicetype select 3,'cccc'
insert into tcl_cms_devicetype select 4,'dddd'
insert into tcl_cms_devicetype select 5,'ee'
insert into tcl_cms_devicetype select 6,'ffff'
insert into tcl_cms_devicetype select 7,'rr'
insert into tcl_cms_devicetype select 10,'xxxx'
insert into tcl_cms_devicetype select 13,'zzz'
go
update tcl_cms_videocloumn set appdevicetypes=','+appdevicetypes+',' where appdevicetypes<>'all'
while exists(select 1 from tcl_cms_videocloumn a inner join tcl_cms_devicetype b on charindex(','+ltrim(b.id)+',',a.appdevicetypes)>0)
update a set appdevicetypes=replace(a.appdevicetypes,','+ltrim(b.id)+',',','+b.devicetype+',')
from tcl_cms_videocloumn a inner join tcl_cms_devicetype b on charindex(','+ltrim(b.id)+',',a.appdevicetypes)>0
where a.appdevicetypes<>'all'
go
update tcl_cms_videocloumn set appdevicetypes=substring(appdevicetypes,2,len(appdevicetypes)-2)
where left(appdevicetypes,1)=','
select * from tcl_cms_videocloumn
/*
appdevicetypes
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
cccc,ee,rr
aaa,bb,cccc,ffff,zzz
all

(3 行受影响)

*/
go
drop table tcl_cms_videocloumn,tcl_cms_devicetype