Below is the code to add the new columns. Each fruit must ultimately be in its own column, and since we have the largest string that has nine fruits, we need nine new columns. If a string has less than nine fruits, and many do, NULL will be the value of those columns above the number of fruits in the string.

To do this we use the ALTER TABLE statement. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. For example, to delete a column in a table, use the following syntax: ALTER TABLE table_name DROP COLUMN column_name;

To change the data type of a column in a table, use the following syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype;

ALTER TABLE ParseFruit
    ADD fruit1 NVARCHAR(50) NULL, 
          fruit2 NVARCHAR(50) NULL,
	  fruit3 NVARCHAR(50) NULL,
	  fruit4 NVARCHAR(50) NULL,
	  fruit5 NVARCHAR(50) NULL,
	  fruit6 NVARCHAR(50) NULL,
	  fruit7 NVARCHAR(50) NULL,
	  fruit8 NVARCHAR(50) NULL,
	  fruit9 NVARCHAR(50) NULL

Now we must copy the fruits over.

UPDATE dbo.ParseFruit SET fruit1 = REVERSE(PARSENAME(REPLACE(REVERSE(Group1), ',', '.'), 1));
UPDATE dbo.ParseFruit SET fruit2 = REVERSE(PARSENAME(REPLACE(REVERSE(Group1), ',', '.'), 2));
UPDATE dbo.ParseFruit SET fruit3 = REVERSE(PARSENAME(REPLACE(REVERSE(Group1), ',', '.'), 3));
UPDATE dbo.ParseFruit SET fruit4 = REVERSE(PARSENAME(REPLACE(REVERSE(Group1), ',', '.'), 4));

UPDATE dbo.ParseFruit SET fruit5 = REVERSE(PARSENAME(REPLACE(REVERSE(Group2), ',', '.'), 1));
UPDATE dbo.ParseFruit SET fruit6 = REVERSE(PARSENAME(REPLACE(REVERSE(Group2), ',', '.'), 2));
UPDATE dbo.ParseFruit SET fruit7 = REVERSE(PARSENAME(REPLACE(REVERSE(Group2), ',', '.'), 3));
UPDATE dbo.ParseFruit SET fruit8 = REVERSE(PARSENAME(REPLACE(REVERSE(Group2), ',', '.'), 4));

UPDATE dbo.ParseFruit SET fruit9 = REVERSE(PARSENAME(REPLACE(REVERSE(Group3), ',', '.'), 1));

Here’s where we’re at.