Eventually of course, I will do a join so it showing the actual parts name, etc. But I am just trying to get the concept down now. Right now no matter what I do, I get 3,3,3 for the Parts for instance. I can’t seem to get rid of the duplicate values. I am just looking for a unique ingredient list per work order without regard to how many times it may appear in the details table or its relationship to other columns.
Somehow missed this part! At the bottom of the code is the final query that uses the CTEs.

Code:
SELECT pp.WorkOrderDetailID
    ,pp.PartsIDFK
    ,pp.Purposes
,p.PartName
,p.PartDescription
/* etc --- you'd add more columns to return up here, just like a normal SQL query */
    ,a.ApprovalCodes
FROM ctePartPurpose pp
    INNER JOIN cteApprovals a
    ON (pp.PartsIDFK = a.PartsIDFK 
        AND 
        pp.WorkOrderDetailID=a.WorkOrderDetailID)
INNER JOIN Parts p ON pp.PartID = p.PartID /* join back to Parts table to get related columns */
What you need to do is join that result to your "Parts" or whatever table that contains the names of the PartNames etc... just a regular join will work. (At that point, you're just writing elementary SQL).