SQL Server Hierarchy ID search for records and include all related parents in result

Question

I've got a Product Category table set up using a SQL Server hierarchy id, I'd like to create an auto-complete dropdown in a CMS that returns searches for matching categories but rather than just displaying the matching category name also displays it's hierarchy/path e.g.

User starts searching for "Cat" and in the auto-complete they see

Pet Products > Cats
Pet Products > Cats > Cat Food
Pet Products > Caterpillars
Pet Activities > Dogs > Catching Games

Is there are simple way to do this? The examples that I've found focus on selecting the parents of a single specific node which is selected first, but I can't find any examples for selecting multiple nodes and then selecting all the parents for the matching nodes.

I'm happy to massage the results into the format outlined above once I've retrieved them from the DB but it's getting the data that seems to be the tricky part.

I'm using SQL Server 2012 (I don't think that matters much though)

Here's my Schema (nothing extraordinary):

CREATE TABLE [dbo].[Category] (
[CategoryId]            INT                     IDENTITY (1, 1) NOT NULL,
[PrimaryProductId]      INT                     NULL,
[CategoryNode]          [sys].[hierarchyid]     NOT NULL,
[CategoryString]        AS                      ([CategoryNode].[ToString]()) PERSISTED,
[CategoryLevel]         AS                      ([CategoryNode].[GetLevel]()) PERSISTED,
[Title]                 NVARCHAR (50)           NOT NULL,
[IsActive]              BIT                     DEFAULT ((1)) NOT NULL,
CONSTRAINT [Category_CategoryId_PK] PRIMARY KEY CLUSTERED ([CategoryId] ASC),
CONSTRAINT [Category_Product_PrimaryProductId] FOREIGN KEY (PrimaryProductId) REFERENCES [dbo].[Product](ProductId),
UNIQUE NONCLUSTERED ([CategoryNode] ASC)

);

Here's an example for parents of a single row:

 DECLARE @person hierarchyid

SELECT @person = Id
FROM dbo.HierachyTable
WHERE [Name] = 'Joe Blow';

SELECT
    Id, Id.ToString() AS [Path], 
    Id.GetLevel() AS [Level],
    Id.GetAncestor(1),
    Name
FROM 
    dbo.HierarchyTable
WHERE 
    @person.IsDescendantOf(Id) = 1

Please show your table schema, sample data. – Sateesh Pagolu Oct 07 '15 at 01:21 — Sateesh Pagolu, Oct 07 '15 at 01:21

score 1 · Accepted Answer · answered Oct 07 '15 at 02:46

Here's a table-valued function that, given a hierarchyid, will return all of the ancestors of the hierarchyid.

alter function dbo.GetAllAncestors(@h hierarchyid, @ReturnSelf bit)
returns table
as return
    select @h.GetAncestor(n.Number) as h
    from dbo.Numbers as n
    where n.Number < @h.GetLevel()

    union all

    select @h
    where @ReturnSelf = 1
go

Where the Numbers table is just a tally table (google for that if you've never heard of it). For your "Joe Blow" example, this should work:

SELECT t.*
FROM dbo.HierarchyTable as h
cross apply dbo.GetAllAncestors(h.CategoryNode, 1) as a
join dbo.HierarchyTable as t
   on a.h = t.CategoryNode
WHERE h.[Name] = 'Joe Blow';

SQL Server Hierarchy ID search for records and include all related parents in result

1 Answers1