Following query will solve your problem
With Test_CTE(Column1,FirstColumn2,SecondColumn2,Column2,FirstColumn3,SecondColumn3,Column3)
AS(
Select column1,SUBSTRING(column2,1, charindex(',',Column2,1)-1) as FirstColumn2,
SUBSTRING(column2,charindex(',',Column2,1)+1,10) SecondColumn2,Column2,
SUBSTRING(column3,1, charindex(',',Column3,1)-1) as FirstColumn3,
SUBSTRING(column3,charindex(',',Column3,1)+1,10) SecondColumn3,Column3 from tab1
)
Select Column1,( CASE WHEN CHARINDEX(FirstColumn2 ,column3,1)> 0 then (CASE WHEN CHARINDEX(SecondColumn2 ,column3,1)> 0 then '' else SecondColumn2 + ' Deleted' end ) else FirstColumn2 + ' Deleted ' end )
+ ' and ' + (CASE WHEN CHARINDEX(FirstColumn3 ,column2,1)> 0 then (CASE WHEN CHARINDEX(SecondColumn3 ,column2,1)> 0 then '' else SecondColumn3 + ' Added' end ) else FirstColumn3 + ' Added ' end) as Result ,Column2,column3 ,CHARINDEX(FirstColumn2 ,column3,1)from TEst_CTE