How to split a comma-separated value to columns in sql server (How to split a single column values to multiple column values? )

Syntax:
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO
CREATE TABLE [dbo].[mytable](
[id] [int] NULL,
[FullName] [varchar](5000) NULL
) ON [PRIMARY]

GO

Insert into mytable values (1,'Test,King,Demo')


mytable:

Id     FullName
---  ----------------
1     Test,King,Demo


ResultTable:

To separate the comma delimited string into 3 columns

Id    FullName                  Name      Surname         Last
---   ------------                -------        -----------          ------
1     Test,King,Demo           Test        King            Demo



Method 1 :-

Use the CommonTableExpression(CTE)

-- Define the CTE expression name and column list.
;WITH Split_Names (ID,Name, xmlname)
AS
-- Define the CTE query.
(
    SELECT ID,
    Name,
    CONVERT(XML,'<Names><name>'
    + REPLACE(YourCommaSeparatedValueColumn,',', '</name><name>') + '</name></Names>') AS xmlname
      FROM YourTableName
)
-- Define the outer query referencing the CTE name.
 SELECT ID,   
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name, 
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname,
 xmlname.value('/Names[1]/name[3]','varchar(100)') AS Last
 FROM Split_Names

Method 2:-

 SELECT DISTINCT id
, S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name
, S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS SurName
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Last
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(fullname, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM mytable) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)