You can use
Common Table Expression[
^] to create cumulate values from previous rows.
For example something like
WITH Cars (CategoryProductID, Text, Parents)
AS (
SELECT CategoryProductID, CategoryText, CategoryText
FROM tSameCategoryProducts
WHERE CategoryProductID = 1
UNION ALL
SELECT CategoryProductID, CategoryText, Parents + '/' + CategoryText
FROM tSameCategoryProducts t inner join cars c
on t.CategoryProductParentID = c.CategoryProductID
)
SELECT Text, Parents
FROM Cars
For more information see
Recursive Queries Using Common Table Expressions[
^]