Call Back In Union All Within the With-As-Table Itself ALTER PROCEDURE [dbo].[A]
AS
BEGIN
WITH SplitSting1
AS(  
Select id, NameString, emp
  ,LEFT(NameString,CHARINDEX(',',NameString)-1) As Head
  ,RIGHT(NameString,LEN(NameString)-CHARINDEX(',',NameString))  AS Remainder
From [dbo].[Table_1]
WHERE NameString IS NOT NULL AND CHARINDEX(',',NameString)&gt;0
Union ALL
Select id, NameString, emp
    ,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
    ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))      
From SplitSting1
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)&gt;0
)
Select * from SplitSting1 order by id option (maxrecursion 0); 
END






The first image is the table I query.
The second image is the output I run my stored procedure.

Can anyone assist to explain how the code is running to generate the output seem like split the NameString/Remainder Column into Head Column. How is the algorithm work?
This is a Recursive Common Table Expression or Recursive CTE. 

References:
Official documentation for SQL Server
(they are supported by other databases too)
A tutorial and another one, both from a very quick Google search so you might find better with more effort now you know the right terms to search for.