Create Table A (ID Int, Name Varchar (5), ProID Varchar (10))
INSERT INTO A
SELECT 1, 'Jia', '1, 2, 3' Union
SELECT 2, 'B', '2, 3'
Create Table B (Proid Int, Name Varchar (10))
INSERT INTO B
SELECT 1, 'Item 1' Union
SELECT 2, 'Item 2' Union
SELECT 3, 'Item 3' Union
SELECT 4, 'Item 4'
------------------achieve------------------------------- ----------------
Create function getk (@ID int)
Returns varchar (500)
AS
Begin
Declare @s varchar (500)
Set @ s = ''
Select @ s = @ s b.Name ',' from a left join b on charindex (Cast (B.Proid as varchar (2)), a.proid> 0 where a.id=@id
Set @ s = left (@ s, len (@S) -1)
Return (@S)
end
---------------------------------------------
SELECT ID, NAME, DBO.GETK (ID) from A
----------------result--------------------------------- ----------------
1 Item 1, Item 2, Item 3
2 B Item 2, Item 3
