SQL Query to List Category Hierarchy

Hi Folks,

Thought I would share this query I wrote for a client.  It shows the full category hierarchy to n levels deep and lists the category name, CategoryID, and ParentcategoryId.   You can run this in sql mgmt. studio or in NopAdmin under Utilities --> Run Sql Commands.     Just change the [YourDBName] reference to your nopCommerce db name.


with
nopC
as
(
SELECT [Id]
      ,[Name]
      
      ,[ParentCategoryId]
      ,CAST(([Name]) AS VARCHAR(1000)) AS "Path"
  FROM [YourDBName].[dbo].[Category]
  where ParentCategoryId =0
  union all
SELECT t.[Id]
      ,t.[Name]
      
      ,t.[ParentCategoryId]
      , CAST((a.path + ' (' + cast(a.Id as varchar(50)) + ')  ' + '--> ' + t.Name +   ' (' + cast(t.Id as varchar(4)) + ')' )  AS VARCHAR(1000)) AS "Path"
  FROM [YourDBName].[dbo].[Category] t
  join nopc as a
  on t.parentcategoryid = a.id)
  select * from nopC order by path

Kind Regards,
Shawn
NopAdmin.com

Continuing swalsh’s spirit of contribution, here are a couple of expansions on hierarchal Category lists.  The first adds a logical sort to the list based on Category DisplayOrder.

WITH CategoryParents(ChildId, ParentId, Sort, Trail)
    AS (   
            SELECT    c.[Id], 
                      c.ParentCategoryId,
                      CONVERT(varchar(2000), RIGHT('00000' + LTRIM(STR(c.DisplayOrder)), 11) + '|') AS  Sort,
                      c.[Name] AS Trail
                FROM dbo.[Category] c
                WHERE c.ParentCategoryId = 0 AND c.[Deleted] = 0
            UNION ALL
            SELECT    c.[Id], 
                      c.ParentCategoryId,
                      CONVERT(varchar(2000), cp.Sort + '|' + RIGHT('00000' + LTRIM(STR(c.DisplayOrder)), 11) + '|') AS Sort,
                      CAST(cp.[Trail] + '->' + c.[Name] AS NVARCHAR(400)) AS Trail
                 FROM dbo.[Category] c
                     JOIN CategoryParents cp ON c.ParentCategoryId = cp.[ChildId]
                 WHERE c.[Deleted] = 0
    ) 
    SELECT *
        FROM CategoryParents cp
            JOIN dbo.[Category] c ON cp.[ChildId] = c.Id
        WHERE c.[Deleted] = 0
        ORDER BY cp.Sort
    OPTION(MAXRECURSION 15)
;

If you need a list that shows all Category hierarchies for each Product, you can use the results returned by the above Common Table Expression (CTE) in a FOR XML PATH expression which will concatenate child row values into a single row.  Here, multiple Category trails for each Product are separated by a semicolon.

;WITH CategoryParents(ChildId, ParentId, Sort, Trail)
    AS (
            SELECT    c.[Id], 
                      c.ParentCategoryId,
                      CONVERT(varchar(2000), RIGHT('00000' + LTRIM(STR(c.DisplayOrder)), 11) + '|') AS  Sort,
                      c.[Name] AS Trail
                FROM dbo.[Category] c
                WHERE c.ParentCategoryId = 0 AND c.[Deleted] = 0
             UNION ALL
             SELECT    c.[Id], 
                       c.ParentCategoryId,
                       CONVERT(varchar(2000), cp.Sort + '|' + RIGHT('00000' + LTRIM(STR(c.DisplayOrder)), 11) + '|') AS Sort,
                       CAST(cp.[Trail] + '->' + c.[Name] AS NVARCHAR(400)) AS Trail
                 FROM dbo.[Category] c
                     JOIN CategoryParents cp ON c.ParentCategoryId = cp.[ChildId]
                 WHERE c.[Deleted] = 0
    )

SELECT TOP 1000    p.[Id] AS ProductId,
                   p.[Name] AS [Name],
                   p.[Sku] AS [SKU],
                   STUFF(    (    SELECT    '; ' + RTRIM(cp.Trail)
                                      FROM CategoryParents cp
                                          JOIN dbo.[Category] c ON cp.[ChildId] = c.Id
                                              JOIN dbo.[Product_Category_Mapping] pcm ON c.ID = pcm.CategoryId
                                      WHERE    pcm.[ProductId] = p.[Id]
                                      ORDER BY    cp.Sort
                                  FOR XML PATH(''),
                                      root('Trail'),
                                      type
                             ).value('/Trail[1]', 'VARCHAR(400)'),
                             1,
                             2,
                             ''
                   ) AS [CategoryTrail]

    FROM    [dbo].[Product] p

    ORDER BY    p.SKU

    OPTION(MAXRECURSION 15)

Hello everybody,

I just stumbled into this topic, I was wondering how should be the query if I would display all the products with their related hierarchy categories and not the orders.

Thank you very much for the support.

kramffud wrote:

RE: "...display all the products with their related hierarchy categories and not the orders"

It says it displays products and their categories.  'ORDER BY ' is a keyword for sorting the results.

swalsh46711 wrote:


You Can Solve That

ALTER  function [dbo].[getCategoryByProductId]
(
  @productId int
)
RETURNS  varchar(400)
AS
BEGIN  

    DECLARE @categoriId int       
  Select TOP(1) @categoriId = CategoryId from Product_Category_Mapping with (NOLOCK) where ProductId=@productId
  
  declare @SubcategoryName nvarchar(400)
  select top(1) @SubcategoryName=Name from Category where Id=@categoriId


  declare @parentCategoryId int
  select top(1)  @parentCategoryId=ParentCategoryId from Category where Id= @categoriId


  declare @categoryName nvarchar(400)
  select top(1) @categoryName = Name from Category where Id= @parentCategoryId


    return  concat(@categoryName,'-',@SubcategoryName)
END