Another way to achieve that is to use CTE (Common Table Expressions) with
Pivot[
^]:
DECLARE @tmp TABLE(eid INT IDENTITY(1,1), ename VARCHAR(30))
INSERT INTO @tmp (ename)
VALUES('kula sekhar reddy'),
('sachin ramesh tendulker'),
('mahindra singh dhoni'),
('ravi chandra aswin')
;WITH CTE AS
(
SELECT 1 AS MyCounter, eid, LEFT(ename, CHARINDEX(' ', ename)-1) AS PartOfName, RIGHT(ename, LEN(ename) - CHARINDEX(' ', ename)) AS Remainder
FROM @tmp
WHERE CHARINDEX(' ', ename)>0
UNION ALL
SELECT MyCounter + 1 AS MyCounter, eid, LEFT(Remainder, CHARINDEX(' ', Remainder)-1) AS PartOfName, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(' ', Remainder)) AS Remainder
FROM CTE
WHERE CHARINDEX(' ', Remainder)>0
UNION ALL
SELECT MyCounter + 1 AS MyCounter, eid, Remainder AS PartOfName , NULL AS Remainder
FROM CTE
WHERE CHARINDEX(' ', Remainder)=0
)
SELECT eid, [1] AS FirstName, [2] As SecondName, [3] AS LastName
FROM (
SELECT eid, MyCounter, PartOfName
FROM CTE
) AS dt
PIVOT(MAX(PartOfName) FOR MyCounter IN ([1], [2], [3])) AS pvt
Result:
eid FirstName SecondName LastName
1 kula sekhar reddy
2 sachin ramesh tendulker
3 mahindra singh dhoni
4 ravi chandra aswin
For further details, please see:
Using Common Table Expressions[
^]
Recursive Queries Using Common Table Expressions[
^]
WITH common_table_expression (Transact-SQL) | Microsoft Docs[
^]