How to pull a related ‘anchor’ value on all recursive rows of a CTE

Using the following website as a starting point (https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/) , I have made a CTE query that looks like:

    WITH q (n, Part_Number, Job, Material) AS (
SELECT
    0,
    Material.Part_Number,
    Material_Req.Job,
    Material_Req.Material
FROM 
    Production.dbo.Material_Req
    INNER JOIN PRODUCTION.dbo.Material ON Material_Req.Job = Material.Job
WHERE e.Job LIKE 'TEMPLATE%' AND e.Material IN  ('PART1')
UNION ALL
SELECT 
    n + 1,
    Material.Part_Number,
    Material_Req.Job,
    Material_Req.Material
FROM 
    Production.dbo.Material_Req
    INNER JOIN PRODUCTION.dbo.Material ON Material_Req.Job = Material.Job
    INNER JOIN q ON q.Material = Material.Material
    WHERE Material.Job LIKE 'TEMPLATE%')
SELECT DISTINCT n, Job, Part_Number, Material FROM q WHERE Job LIKE 'TEMPLATE%'
ORDER BY n

which produces results that would look like the table below, which is relatively navigable, especially if the end-user knows the content.

n	Job	Part_Number	Material
0	TEMP1	PART1	PART1a
0	TEMP1	PART1	PART1b
1	TEMP2	PART1a	MATERIAL1
1	TEMP3	PART1b	PART1b1
2	TEMP4	PART1b1	PART1b1a
3	TEMP5	PART1b1a	MATERIAL2

However, if I were to include multiple PART’s in the WHERE condition on the anchor, e.g.

WHERE e.Job LIKE 'TEMPLATE%' AND e.Material IN  ('PART1', 'PART2', ..., 'PART99')

The resulting table would be exponentially harder to navigate.

I would like to be able to somehow append the original anchor ‘PART’ that the recursive row originated from. Is this even possible? I cannot comprehend how. The resulting table would look something like:

n	Job	Part_Number	Material	Anchor
0	TEMP1	PART1	PART1a	PART1
0	TEMP1	PART1	PART1b	PART1
0	TEMP8	PART2	PART2a	PART2
1	TEMP2	PART1a	MATERIAL1	PART1
1	TEMP3	PART1b	PART1b1	PART1
1	TEMP9	PART2a	PART2a1	PART2
2	TEMP4	PART1b1	PART1b1a	PART1
2	TEMP10	PART2a1	MATERIAL7	PART2
3	TEMP5	PART1b1a	MATERIAL2	PART1

Answer

If I am understand correctly, you just need to expose the Part_Number column twice in then “upper” SELECT and then put the CTE’s column in the “lower” one:

WITH q AS
    (SELECT 0 AS n,
            Material.Part_Number,
            Material_Req.Job,
            Material_Req.Material,
            Material.Part_Number AS Anchor
     FROM Production.dbo.Material_Req
          INNER JOIN PRODUCTION.dbo.Material ON Material_Req.Job = Material.Job
     WHERE e.Job LIKE 'TEMPLATE%'
       AND e.Material IN ('PART1')
     UNION ALL
     SELECT n + 1,
            Material.Part_Number,
            Material_Req.Job,
            Material_Req.Material,
            q.Anchor
     FROM Production.dbo.Material_Req
          INNER JOIN PRODUCTION.dbo.Material ON Material_Req.Job = Material.Job
          INNER JOIN q ON q.Material = Material.Material
     WHERE Material.Job LIKE 'TEMPLATE%')
SELECT DISTINCT
       n,
       Job,
       Part_Number,
       Material,
       anchor
FROM q
WHERE Job LIKE 'TEMPLATE%'
ORDER BY n;