• 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;