分解字符串包含的信息值后然后合并到另外一表的信息

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-26 14:38:22

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   : http://blog.youkuaiyun.com/htl258

--  Subject:分解字符串包含的信息值后然后合并到另外一表的信息

--------------------------------------------------------------------------

/*问题描述

表Library有字段         表EmployeeInfo  字段
LibraryId                         EmpId
EmpId
LibraryTitle
LibraryOwner                      Name

测试表Library数据为
insert into Library(EmpId,LibraryTitle,Name) values('001','规章制度','001')
insert into Library(EmpId,LibraryTitle,Name) values('001','公共信息','001,002,003')
测试表EmployeeInfo  数据为
insert into EmployeeInfo  (EmpId,Name) values('001','小王')
insert into EmployeeInfo  (EmpId,Name) values('002','小陈)
insert into EmployeeInfo  (EmpId,Name) values('003','小张')

有什么办法能在前台显示的时候显示为
EmpId  LibraryTitle   Name
001    规章制度       小王
001    公共信息       小王,小陈，小张

*/

 

create table Library(EmpId varchar(10),LibraryTitle varchar(10),Name varchar(30))

insert into Library(EmpId,LibraryTitle,Name)

    values('001','规章制度','001')

insert into Library(EmpId,LibraryTitle,Name)

    values('001','公共信息','001,002,003')

create table EmployeeInfo(EmpId varchar(10),Name varchar(10))

insert into EmployeeInfo  (EmpId,Name) values('001','小王')

insert into EmployeeInfo  (EmpId,Name) values('002','小陈')

insert into EmployeeInfo  (EmpId,Name) values('003','小张')

go

 

create function fn_tonytest(@name varchar(100))

returns varchar(100)

as

begin

    select @name=replace(@name,EmpId,name) from EmployeeInfo

    return @name

end

go

 

select EmpId,LibraryTitle,dbo.fn_tonytest(Name) Name 

from Library

/*

EmpId  LibraryTitle  Name

001 规章制度 小王

001 公共信息 小王,小陈,小张

*/