Retrieving Hierarchical Data from a single table

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.

Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure.
Example data:

ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level
-------------------------------------------
1 Custom Furniture 0 0
2 Boxes 0 0
3 Toys 0 0
4 Bedroom 1 Custom Furniture 1
5 Dining 1 Custom Furniture 1
6 Accessories 1 Custom Furniture 1
7 Picture Frames 6 Accessories 2
8 Serving Trays 6 Accessories 2
9 Entertainment 1 Custom Furniture 1
10 Planes 3 Toys 1
11 Trains 3 Toys 1
12 Boats 3 Toys 1
13 Automobiles 3 Toys 1
14 Jewelry 2 Boxes 1
15 Keepsake 2 Boxes 1
16 Specialty 2 Boxes 1

Desired output:

Custom Furniture
Accessories
Picture Frames
Serving Trays
Bedroom
Dining
Entertainment
Boxes
Jewelry
Keepsake
Specialty
Toys
Automobiles
Boats
Planes
Trains

Hello, if I get you right, here is a very short article that might get you started:http://www.mmkit.com/article.php?sid=345&lang=en_GB

HTH. -LV

|||The answer very much depends on whether you are using Sql Server 2005 or an older version. Assuming 2005 then have a read ofHeirarchical Queries in Sql Server 2005.|||Thanks much. This was exactly what I needed. I don't think I could have come up with this solution on my on at this point.|||

--Copy the code to run on your machine. I named your table as TreeSource with columns needed.

IF(SELECTOBJECT_ID('TreeSource','U'))ISNOTNULL

BEGIN

DROPTABLE TreeSource

END

GO

CREATETABLE [dbo].[TreeSource](

[ProductCategoryID] [int]NULL,

[CatDescription] [nvarchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentProductCategoryID] [int]NULL

)ON [PRIMARY]

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(1,'Custom Furniture',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(2,'Boxes',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(3,'Toys',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(4,'Bedroom',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(5,'Dining',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(6,'Accessories',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(7,'Picture Frames',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(8,'Serving Trays',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(9,'Entertainment',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(10,'Planes',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(11,'Trains',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(12,'Boats',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(13,'Automobiles',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(14,'Jewelry',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(15,'Keepsake',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(16,'Specialty',2)

--step 0

CREATETABLE #Tree(

NodeintNOTNULLIDENTITY(100, 1),

ParentNodeint,

ProductCategoryIDintNOTNULL,

Depthtinyint,

Lineagevarchar(50))

--step 1

INSERTINTO #Tree(ProductCategoryID)SELECT ProductCategoryIDFROM TreeSourceORDERBY ParentProductCategoryID,CatDescription

Go

UPDATE TSET T.ParentNode=P.Node

FROM dbo.#Tree T

INNERJOIN TreeSource EON T.ProductCategoryID=E.ProductCategoryID

INNERJOIN TreeSource BON E.ParentProductCategoryID=B.ProductCategoryID

INNERJOIN dbo.#Tree PON B.ProductCategoryID=P.ProductCategoryID

GO

--step 3

UPDATE #TreeSET Lineage='.', Depth=0WHERE ParentNodeIsNull

--step 4

WHILEEXISTS(SELECT*FROM #TreeWHERE DepthIsNull)

UPDATE TSET T.depth= P.Depth+ 1,

T.Lineage= P.Lineage+Ltrim(Str(T.ParentNode,4,0))+'.'

FROM #TreeAS T

INNERJOIN #TreeAS PON(T.ParentNode=P.Node)

WHERE P.Depth>=0

AND P.LineageIsNotNull

AND T.DepthIsNull

--step 5 final

SELECTSpace(T.Depth*4)+ E.CatDescriptionASName

FROM TreeSource E

INNERJOIN #Tree TON E.ProductCategoryID=T.ProductCategoryID

ORDERBY T.Lineage+Ltrim(Str(T.Node,6,0))

--SELECT * FROM #Tree

DROPTable #Tree

--read more http://www.sqlteam.com/item.asp?ItemID=8866