-- converting column with commas to multiple columns --
declare @col1 varchar(500)
set @col1 = 'I,Hate,Broccoli'
DECLARE @Tmp TABLE ( Id int, Element VARCHAR(20))
INSERT @Tmp SELECT 1,@col1
SELECT Id,
PARSENAME(REPLACE(Element,',','.'),2) Name,
PARSENAME(REPLACE(Element,',','.'),1) Surname
FROM @Tmp
-- converting column with commas to multiple rows --
declare @col1 varchar(500)
set @col1 = 'I,am,really,a,smart person, one of the smartest'
DECLARE @Tmp TABLE ( Id int, Element VARCHAR(200))
INSERT @Tmp SELECT 1,@col1
SELECT A.[id],
Split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT [id],
CAST ('<M>' + REPLACE(Element, ',', '</M><M>') + '</M>' AS XML) AS String
FROM @Tmp) AS A CROSS APPLY String.nodes ('/M') AS Split(a);