You could use a recursive cte to split the strings:

WITH rep AS

(

SELECT

cod

,';' delim

,name

from t

UNION ALL

SELECT

LEFT(cod, CHARINDEX(delim, cod, 1) - 1)

,delim

,LEFT(name, CHARINDEX(delim, name, 1) - 1)

FROM rep

WHERE (CHARINDEX(delim, cod,...

Sounds like you need a couple of left joins and a case - this should do...

SELECT

s.sno

,CASEWHEN e.sno IS NOT NULL THEN

'Y'

WHEN n.sno IS NOT NULL THEN

'N'

WHEN e.sno IS NULL...