---创建测试环境
Create Table Tags(ID int,Name Varchar(20))
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 Test(ID int,Title Varchar(20),TagID Varchar(20))
Insert Test 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 dbo.fnTest(@ID Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @sql Varchar(8000)
Set @sql= ' '
Select @sql=@sql+ ', '+[Name] From Tags Where Charindex(rtrim(ID),@ID) >0
Return Stuff(@sql,1,1, ' ')
End
Go
---调用自定义函数查询结果
Select ID,Title,dbo.fnTest(TagID) As Tags From Test
---删除测试环境
Drop Table Tags,Test
Drop Function fnTest
---结果
/*
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
(所影响的行数为 5 行)
*/
Create Table Tags(ID int,Name Varchar(20))
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 Test(ID int,Title Varchar(20),TagID Varchar(20))
Insert Test 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 dbo.fnTest(@ID Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @sql Varchar(8000)
Set @sql= ' '
Select @sql=@sql+ ', '+[Name] From Tags Where Charindex(rtrim(ID),@ID) >0
Return Stuff(@sql,1,1, ' ')
End
Go
---调用自定义函数查询结果
Select ID,Title,dbo.fnTest(TagID) As Tags From Test
---删除测试环境
Drop Table Tags,Test
Drop Function fnTest
---结果
/*
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
(所影响的行数为 5 行)
*/