we can do without using master.dbo.DelimitedSplit8K()

SELECT A.employeeid,A.employeename,B.categoryname,C.categoryname FROM

(

SELECT employeeid,employeename,

SUBSTRING(categoryids,1,CHARINDEX(',',categoryids)-1) categoryid1,

SUBSTRING(categoryids,CHARINDEX(',',categoryids)+1,LEN(categoryids)) categoryid2

FROM

tblemployees

)A

INNER JOIN

tblcategories B ON A.categoryid1=B.categoryid

INNER JOIN

tblcategories C ON A.categoryid2=C.categoryid

i hope this will be useful to someone:-)