CREATE TABLE #T (Col1 VARCHAR(200)
				, Col2 VARCHAR(200)
				, Col3 VARCHAR(200)
				, Col4 VARCHAR(200))
INSERT INTO #T (Col1) VALUES ('Apple, Pear, Orange')
INSERT INTO #T (Col1) VALUES ('Pear, Apple, Bananna')
INSERT INTO #T (Col1) VALUES ('Pear, Apple, Bananna, Lemon')
INSERT INTO #T (Col1) VALUES ('Pear, Apple')
INSERT INTO #T (Col1) VALUES ('Kiwi')
GO

SELECT * FROM #T

UPDATE #T
SET Col1 = LEFT(Col1, CHARINDEX(',', Col1) - 1)
	, Col2 = LTRIM(SUBSTRING(Col1, CHARINDEX(',', Col1) + 1, LEN(Col1)))
WHERE CHARINDEX(',', Col1) > 0

UPDATE #T
SET Col2 = LEFT(Col2, CHARINDEX(',', Col2) - 1)
	, Col3 = LTRIM(SUBSTRING(Col2, CHARINDEX(',', Col2) + 1, LEN(Col2)))
WHERE CHARINDEX(',', Col2) > 0

UPDATE #T
SET Col3 = LEFT(Col3, CHARINDEX(',', Col3) - 1)
	, Col4 = LTRIM(SUBSTRING(Col3, CHARINDEX(',', Col3) + 1, LEN(Col3)))
WHERE CHARINDEX(',', Col3) > 0

SELECT * FROM #T
GO

DROP TABLE #T
GO