改下一行試試

SELECT level,id,@SearchTerm as ProductName,Parent,Child FROM  cte ;

或是改

DECLARE @SearchTerm NVARCHAR(10) = N'carA';
DECLARE @Table AS TABLE ( id INT, product1 NVARCHAR(10), product2 NVARCHAR(10));

INSERT INTO @Table
VALUES ( 1, 'carA', 'Tire' )
,      ( 2, 'carA', 'meter' )
,      ( 3, 'Tire', 'tireA' )
,      ( 4, 'Tire', 'tireB' )
,      ( 5, 'meter', 'meterA' )
,      ( 6, 'meter', 'meterB' )
,      ( 7, 'meter', 'meterC' )
,      ( 8, 'meterC', 'meterCC' );


WITH cte AS ( SELECT    1           AS level
              ,         T1.id
              ,@SearchTerm as ProductName
              ,         T1.product1 Parent
              ,         T1.product2 Child
                FROM    @Table T1
               WHERE T1.product1 = @SearchTerm
              UNION ALL
              SELECT    cte.level + 1
              ,         T2.id
              ,@SearchTerm as ProductName
              ,         T2.product1 Parent
              ,         T2.product2 Child
                FROM    @Table T2
                JOIN cte ON cte.Child = T2.product1 )
                
SELECT  *  FROM  cte ;

Demo