The whole premise of this question just seems... flawed.
You will need to provide some more context on what the actual needs are (written business) as the logic for the "required" result seems to be incorrect.
That said; this looks like this should be an everyday parent-child table relationship; with tables for Fruits (parent) and Varieties (children).
DECLARE @Fruits TABLE (
FruitID INT PRIMARY KEY NOT NULL,
FruitName NVARCHAR(16) NULL
)
DECLARE @Varieties TABLE (
VarietyID INT PRIMARY KEY NOT NULL,
FruitID INT NULL,
VarietyName NVARCHAR(16) NULL,
VarietyPrice INT NULL,
WorthlessID INT NULL
)
INSERT @Fruits (FruitID, FruitName)
VALUES (1, 'Apple')
, (2, 'Orange')
INSERT @Varieties(VarietyID, FruitID, VarietyName, VarietyPrice, WorthlessID)
VALUES (1, 1, 'Fuji', 5, 1)
, (2, 1, 'Gala', 6, 2)
, (3, 1, 'Carrot', 6, 3)
, (4, 1, 'Tamota', 5, 5)
, (5, 2, 'Valencia', 4, 4)
, (6, 2, 'Navel', 5, 5)
, (7, 2, 'Banana', 5, 5)
, (8, 2, 'Fuji', 5, 1)
, (9, 2, 'Gala', 6, 2)
SELECT v.VarietyID, f.FruitName, v.VarietyName, v.VarietyPrice
FROM @Fruits f
JOIN @Varieties v ON f.FruitID = v.FruitID
Which returns
VarietyID FruitName VarietyName VarietyPrice WorthlessID
----------- ---------------- ---------------- ------------ -----------
1 Apple Fuji 5 1
2 Apple Gala 6 2
3 Apple Carrot 6 3
4 Apple Tamota 5 5
5 Orange Valencia 4 4
6 Orange Navel 5 5
7 Orange Banana 5 5
8 Orange Fuji 5 1
9 Orange Gala 6 2