SQL Server 2022

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

CREATE TABLE temp (
  product_identifier int,
  parent_product_identifier int,
  Zone varchar(10)
)

INSERT INTO temp (product_identifier,parent_product_identifier, zone)
  VALUES
  (1,5,   'E'),
  (2, 6,   'F'),
  (3, 7,   'G'),
  (4, 8,   'H'),
  (5, 11,  'R' ),
  (6, 12,  'B'),
  (7, 13,  'C'),
  (8, 14,  'D'),
  (11,15,  'A');

with dt as (
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent
  FROM temp

UNION ALL

SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1,
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1
AND hierarchy = (SELECT MAX(hierarchy) FROM dt dt2 WHERE dt2.product_identifier = dt.product_identifier)
ORDER BY product_identifier;

product_identifier	parent_product_identifier	hierarchy	Zone
1	5	3	A
2	6	2	B
3	7	2	C
4	8	2	D
5	11	2	A