Product Category Hierarchy Exploded

Product Category Hierarchy Exploded

Submitted by Nathan Clouse on 10/02/15.

A view that shows the Product Category Hierarchy Exploded in full by product. This will give you a list from top to bottom of all categories for a product and where those categories belong in a category hierarchy.

AX Version: 

Module: 

CREATE VIEW [dbo].[vw_pimEcoResCategories] 
AS 

WITH category ( NAME, level_, categoryhierarchy, parentcategory, recid, product, partition ) 
     AS (SELECT ec.NAME, 
                ec.level_, 
                ec.categoryhierarchy, 
                ec.parentcategory, 
                ec.recid, 
                erpc.product, 
                ec.partition 
         FROM   ecorescategory ec 
                INNER JOIN ecoresproductcategory erpc 
                        ON erpc.categoryhierarchy = ec.categoryhierarchy 
                           AND erpc.category = ec.recid 
                           AND erpc.partition = ec.partition 
         UNION ALL 
         SELECT ec.NAME, 
                ec.level_, 
                ec.categoryhierarchy, 
                ec.parentcategory, 
                ec.recid, 
                category.product, 
                ec.partition 
         FROM   ecorescategory ec 
                INNER JOIN category 
                        ON ec.recid = category.parentcategory 
                           AND ec.categoryhierarchy = ec.categoryhierarchy 
                           AND ec.partition = category.partition) 
SELECT * 
FROM   category 


GO