Splitting comma separated column’s values in rows in SQL Server And Vice Versa

I have seen many Developer/DBA uses user defined function or stored procedure to split a comma separated ( or any delimiter to separate values like |,;-.& or any character/special character) column into rows using various coding logic like while/for loop or using cursor etc.

Let see an example, below is a sample data,

     dbaraja.blogspot.in                  

                  

And my requirement is to split the every SkillSet column values in a single row according EMPID and EMPNAME like

          dbaraja.blogspot.in                                                                   

                                                                           

To get above desired result without using any Function, Stored Procedure or any loop, simple flat SQL select statement will be like this,

SELECT EmpID,EmpName,Split.Data.value('.', 'VARCHAR(100)') AS Skill

FROM (Select EmpID,EmpName,CAST('<M>' + REPLACE(SkillSet,',', '</M><M>')+ '</M>' AS XML) AS Skill FROM Rajendra_Test) AS Data 

CROSS APPLY Skill.nodes ('/M') AS Split(Data)

                           ------------------------------------------------
The below Query is used to retrieve the opposite of above process.

;WITH CTE AS
(
SELECT DISTINCT EmpId
FROM Employee
)
SELECT EmpId, STUFF((SELECT ', ' + CAST(E2.Skill AS NVARCHAR(MAX))
     FROM Employee E2 WHERE E1.EmpId = E2.EmpId
     FOR XML PATH('')),1,1,'') skills
  into Employee1
FROM CTE E1

Select * from Employee1 --Getting Result--