SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Fruit (
    FruitID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255),
    Data text,
    Description varchar(255)
)

INSERT INTO Fruit (Name, Data, Description)
VALUES ('Banana', 'key 1=value 1' + CHAR(13)+CHAR(10) + 'key 2=value 2' + CHAR(13)+CHAR(10) + 'key 3=value 3' + CHAR(13)+CHAR(10) + 'key 4=value 4' + CHAR(13)+CHAR(10) + 'key 5=value 5','Delicious');

INSERT INTO Fruit (Name, Data, Description)
VALUES ('Pear', 'key 1=value 1' + CHAR(13)+CHAR(10) + 'key 2=value 2' + CHAR(13)+CHAR(10) + 'key 3=value 3' + CHAR(13)+CHAR(10) + 'key 4=value 4' + CHAR(13)+CHAR(10) + 'key 5=value 5','Rotton');

INSERT INTO Fruit (Name, Data, Description)
VALUES ('Kiwi', 'key 1=value 1' + CHAR(13)+CHAR(10) + 'key 2=value 2' + CHAR(13)+CHAR(10) + 'key 3=value 3' + CHAR(13)+CHAR(10) + 'key 4=value 4' + CHAR(13)+CHAR(10) + 'key 5=value 5','Okay');

SELECT * FROM Fruit


FruitID
Name
Data
Description


1
Banana
key 1=value 1
key 2=value 2
key 3=value 3
key 4=value 4
key 5=value 5
Delicious

2
Pear
key 1=value 1
key 2=value 2
key 3=value 3
key 4=value 4
key 5=value 5
Rotton

3
Kiwi
key 1=value 1
key 2=value 2
key 3=value 3
key 4=value 4
key 5=value 5
Okay


WITH KP AS
(
    SELECT FruitID, Name, Description, value as KPair
    FROM   Fruit
    CROSS APPLY STRING_SPLIT(REPLACE(CAST(Data AS varchar(max)), CHAR(13)+CHAR(10), ','), ',') /* STRING_SPLIT only allows nchar(1),  varchar(1) */
)
SELECT
    FruitID, Name, Description, KPair, 
    SUBSTRING(KPair, 5, CHARINDEX('=', KPair) - 5) AS [Key],
    SUBSTRING(KPair, CHARINDEX('=', KPair) + 7, LEN(KPair) - CHARINDEX('=', KPair) - 6) AS [Value]
FROM
    KP


FruitID
Name
Description
KPair
Key
Value


1
Banana
Delicious
key 1=value 1
1
1

1
Banana
Delicious
key 2=value 2
2
2

1
Banana
Delicious
key 3=value 3
3
3

1
Banana
Delicious
key 4=value 4
4
4

1
Banana
Delicious
key 5=value 5
5
5

2
Pear
Rotton
key 1=value 1
1
1

2
Pear
Rotton
key 2=value 2
2
2

2
Pear
Rotton
key 3=value 3
3
3

2
Pear
Rotton
key 4=value 4
4
4

2
Pear
Rotton
key 5=value 5
5
5

3
Kiwi
Okay
key 1=value 1
1
1

3
Kiwi
Okay
key 2=value 2
2
2

3
Kiwi
Okay
key 3=value 3
3
3

3
Kiwi
Okay
key 4=value 4
4
4

3
Kiwi
Okay
key 5=value 5
5
5


WITH KP AS
(
    SELECT FruitID, Name, Description, value as KPair
    FROM   Fruit
    CROSS APPLY STRING_SPLIT(REPLACE(CAST(Data AS varchar(max)), CHAR(13)+CHAR(10), ','), ',') /* STRING_SPLIT only allows nchar(1),  varchar(1) */
)
, KP1 AS
(
  SELECT
      FruitID,  
      SUBSTRING(KPair, 5, CHARINDEX('=', KPair) - 5) AS [Key],
      SUBSTRING(KPair, CHARINDEX('=', KPair) + 7, LEN(KPair) - CHARINDEX('=', KPair) - 6) AS [Value]
  FROM
      KP
)
SELECT [FruitID], [1],[2],[3],[4],[5]
FROM   KP1
PIVOT (MAX([Value]) FOR [Key] IN ([1],[2],[3],[4],[5])) AS PVT


FruitID
1
2
3
4
5


1
1
2
3
4
5

2
1
2
3
4
5

3
1
2
3
4
5

FruitID	Name	Data	Description
1	Banana	key 1=value 1 key 2=value 2 key 3=value 3 key 4=value 4 key 5=value 5	Delicious
2	Pear	key 1=value 1 key 2=value 2 key 3=value 3 key 4=value 4 key 5=value 5	Rotton
3	Kiwi	key 1=value 1 key 2=value 2 key 3=value 3 key 4=value 4 key 5=value 5	Okay

FruitID	Name	Description	KPair	Key	Value
1	Banana	Delicious	key 1=value 1	1	1
1	Banana	Delicious	key 2=value 2	2	2
1	Banana	Delicious	key 3=value 3	3	3
1	Banana	Delicious	key 4=value 4	4	4
1	Banana	Delicious	key 5=value 5	5	5
2	Pear	Rotton	key 1=value 1	1	1
2	Pear	Rotton	key 2=value 2	2	2
2	Pear	Rotton	key 3=value 3	3	3
2	Pear	Rotton	key 4=value 4	4	4
2	Pear	Rotton	key 5=value 5	5	5
3	Kiwi	Okay	key 1=value 1	1	1
3	Kiwi	Okay	key 2=value 2	2	2
3	Kiwi	Okay	key 3=value 3	3	3
3	Kiwi	Okay	key 4=value 4	4	4
3	Kiwi	Okay	key 5=value 5	5	5