How about this?

CREATE FUNCTION dbo.SplitString ( @string varchar(4000))

RETURNS @Result TABLE(SpiltedString VARCHAR(100))

AS

BEGIN

DECLARE @xml XML

SELECT @xml = CAST('<VALUE>'+ REPLACE(@string,',','</VALUE><VALUE>')+ '</VALUE>' AS XML)

INSERT INTO @Result

SELECT t.value('.', 'VARCHAR(100)') AS inVal

FROM @xml.nodes('/VALUE') AS x(t)

RETURN

END

GO

Create table #MYTEMP

(Id int ,

value nvarchar(100),

name varchar(50))

insert into #MYTEMP VALUES(1,'B,C,D','XYZ')

SELECT ID, fnResult.SpiltedString, name

FROM #MYTEMP

CROSS APPLY DBO.SplitString(value) as fnResult

DROP FUNCTION dbo.SplitString

DROP TABLE #MYTEMP