NestedTree Structure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

NestedTree Structure

Author

Topic

chrisg229
Starting Member

12 Posts

Posted - 2008-01-04 : 22:42:19

I need to create a table that supports a potentially infinite amount of subcategories.
An example would be:
Food
Pastries(Parent=Food)
Cupcakes(Parent=Pastries)
Chocolate(Parent=Cupcakes)
Vanilla(Parent=Cupcakes)
Danish(Parent=Pastries)
Meat(Parent=Food)
Beef(Parent=Meat)
Chicken(Parent=Meat)
Cajun(Parent=Chicken)
Fried(Parent=Chicken)

The subcategories will vary in depth.

Can someone demonstrate a table create statement and then the proper select to pull this type of data?

Many thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 04:48:02

Table structure will be some thing like

CREATE TABLE CategoriesTable
(
CategoryID int IDENTITY(1,1) PRIMARY KEY,
Category varchar(100),
ParentCategoryID int DEFAULT 0 REFERENCES CategoriesTable (CategoryID)
)

and you can make use of recursive CTE to retrieve data like:-

With Category_CTE (Level,CategoryID,Category) AS
(
SELECT 0,CategoryID,Category--Root level record. this forms anchor member
FROM CategoriesTable
WHERE ParentCategoryID=0

UNION ALL

SELECT Level+1,c.CategoryID,c.Category--Child records.this forms recursive member
FROM CategoriesTable c
INNER JOIN Category_CTE ccte
ON ccte.CategoryID=c.ParentCategoryID
)

SELECT * FROM Category_CTE --gets you full hierarchy

chrisg229
Starting Member

12 Posts

Posted - 2008-01-07 : 13:57:32

Thanks visakh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources