-- 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);