So far we’ve created a lot of new columns. These are only temporary columns because what we really want to get to is a brand new table that contains the fruits for each person. Once this is done we won’t need to be searching strings for certain fruits in our query. That’s costly. We will have a single column that lists all of the fruits and their owners via a foreign key. We’ll be able to write queries that use a join to link the main table to this new table containing all the fruits.

Here below is a create table script that SSMS generated. It creates a new table called NameFruit. To get this script, I first manually created the table in SSMS by right-clicking Tables under the database, then clicking New, then Table… After the table is created you can generate a script by right-clicking the table, Script Table as..Create To, New Query Window.

IF OBJECT_ID(N'[dbo].[NameFruit]', N'U') IS NOT NULL
    DROP TABLE [dbo].[NameFruit];

CREATE TABLE [dbo].[NameFruit](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[NameId] [int] NOT NULL,
	[Fruit] [nvarchar](50) NULL,
 CONSTRAINT [PK_NameFruit] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Now we need to copy the data over from our nine columns into our new table. There is no need to copy over NULL so we’ve included a WHERE clause to handle that.

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit1
	FROM dbo.ParseFruit
	WHERE fruit1 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit2
	FROM dbo.ParseFruit
	WHERE fruit2 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit3
	FROM dbo.ParseFruit
	WHERE fruit3 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit4
	FROM dbo.ParseFruit
	WHERE fruit4 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit5
	FROM dbo.ParseFruit
	WHERE fruit5 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit6
	FROM dbo.ParseFruit
	WHERE fruit6 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit7
	FROM dbo.ParseFruit
	WHERE fruit7 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit8
	FROM dbo.ParseFruit
	WHERE fruit8 IS NOT NULL

INSERT INTO dbo.NameFruit (NameId, Fruit)
	SELECT Id, fruit9
	FROM dbo.ParseFruit
	WHERE fruit9 IS NOT NULL

Here is a screenshot of our new table.