Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?
If I am Understanding you correctly, you are looking to select a group of product that's already in a DB, and assign a new Product ID to each Product that is Unique to each Product Category, if this is correct then you can add this to your SELECT statement,

ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY ID) AS ProductCode

it should do what you are looking for.