I don't see a need for a recursive query:
IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car;
CREATE TABLE Car (
CarID INT,
CarName VARCHAR(16)
);
IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart;
CREATE TABLE CarPart (
PartID INT,
PartName VARCHAR(16),
CarID INT
);
INSERT INTO Car
VALUES (1, 'Chevy'),
(2, 'Ford'),
(3, 'Toyota'),
(4, 'Honda'),
(5, 'Nissan'),
(6, 'Hugo');
INSERT INTO CarPart
VALUES (110, 'Engine', 1),
(120, 'Engine', 2),
(210, 'Door', 1),
(220, 'Door', 3),
(310, 'Seat', 4),
(320, 'Seat', 5),
(410, 'Window', 3),
(510, 'Wheel', 2),
(420, 'Window', 6);
select
c.CarName,
cp.PartName
from
dbo.Car c
inner join dbo.CarPart cp
on (c.CarID = cp.CarID)
order by
c.CarName;
IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car;
IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart;