As promised, here is the follow-up to the last post. This query gets us the names of all subcategories of a particular category in Commerce Server 2007.
DECLARE @categoryOid AS INT SET @categoryOid = 937 ; WITH Cats2 ( oid, child_oid, hierarchy_level ) AS ( SELECT oid , child_oid , 1 AS hierarchy_level FROM dbo.ConsumerShop_CatalogHierarchy WHERE oid = @categoryOid AND CatalogName = 'ConsumerStaging' UNION ALL SELECT h.oid , h.child_oid , c.hierarchy_level + 1 AS hierarchy_level FROM dbo.ConsumerShop_CatalogHierarchy h JOIN Cats2 c ON h.oid = c.child_oid AND h.CatalogName = 'ConsumerStaging' ) SELECT CategoryName FROM dbo.ConsumerShop_CatalogProducts WHERE oid IN ( SELECT DISTINCT ( oid ) FROM Cats2 )