Solved

Get Path Enumeration From either Adjacency List or Nested Set Model

Posted on 2007-04-01
1
463 Views
Last Modified: 2012-08-14
I have a categories table like this:

Create Table Category
(
CatID   Int Null,
CatName Varchar(20),
ParentID Int,
Lft Int,
Rgt Int)


Insert Into Category Values(1, 'Category 1', Null, 1, 14);
Insert Into Category Values(2, 'Category 2', 1, 2, 7);
Insert Into Category Values(3, 'Category 3', 1, 8, 13);
Insert Into Category Values(4, 'Category 4', 3, 9, 10);
Insert Into Category Values(5, 'Category 5', 3, 11, 12);
Insert Into Category Values(6, 'Category 6', 2, 3, 4);
Insert Into Category Values(7, 'Category 7', 2, 5, 6);

It uses both the Adjacency List Model and the Nested Sets Model to represent a hierarchy. I have both models for certain reasons, like backward compatibility with an external application.

Now, additionally I also need to use the Enumeration Path model in this table, so I added columns like this:

Create Table Category
(
CatID   Int Null,
CatName Varchar(20),
ParentID Int,
Lft Int,
Rgt Int,
Level1 Int,
Level2 Int,
Level3 Int,
Level4 Int,
Level5 Int
)

Where I want to store on columns Level1 to Level5 the categories enumerated from Parent to Child. Note that it is from parent to child.
Again, for compatiblity with an external application, I need to provide de enumerator path as separate columns, not as a concatenated string.

(Note:
To get them from Child to Parent is easier, but that's not the way I need it.
I even know how to do it:

Select
X01.CatID, X02.CatID, X03.CatID, X04.CatID, X05.CatID
From Category X01 LEFT OUTER JOIN Category X02 On (X01.ParentID = X02.CatID)
                  LEFT OUTER JOIN Category X03 On (X02.ParentID = X03.CatID)
                  LEFT OUTER JOIN Category X04 On (X03.ParentID = X04.CatID)
                  LEFT OUTER JOIN Category X05 On (X03.ParentID = X04.CatID)

It would look like:
Level1 Level2 Level3 Level4 Level5
1      NULL      NULL      NULL      NULL
2      1      NULL      NULL      NULL
3      1      NULL      NULL      NULL
4      3      1      NULL      NULL
5      3      1      NULL      NULL
6      2      1      NULL      NULL
7      2      1      NULL      NULL


But, again, this is NOT what I need.

).

The way I need it to look like, is like this:
Level1 Level2 Level3 Level4 Level5
1      Null      Null      Null      Null
1      2      Null      Null      Null
1      3      Null      Null      Null
1      3      4      Null      Null
1      3      5      Null      Null
1      2      6      Null      Null
1      2      7      Null      Null

 
Is this possible ?
0
Comment
Question by:fischermx
1 Comment
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
Comment Utility
select x1.catid l1,x2.catid l2,x3.catid l3,x4.catid l4,x5.catid l5
from category x1
left outer join category x2 on x1.catid=x2.parentid
left outer join category x3 on x2.catid=x3.parentid
left outer join category x4 on x3.catid=x4.parentid
left outer join category x5 on x4.catid=x5.parentid
where x1.catid=1
union
select x1.catid l1,x2.catid l2,x3.catid l3,x4.catid l4,null l5
from category x1
left outer join category x2 on x1.catid=x2.parentid
left outer join category x3 on x2.catid=x3.parentid
left outer join category x4 on x3.catid=x4.parentid
where x1.catid=1
union
select x1.catid l1,x2.catid l2,x3.catid l3,null l4,null l5
from category x1
left outer join category x2 on x1.catid=x2.parentid
left outer join category x3 on x2.catid=x3.parentid
where x1.catid=1
union
select x1.catid l1,x2.catid l2,null l3,null l4,null l5
from category x1
left outer join category x2 on x1.catid=x2.parentid
where x1.catid=1
union
select x1.catid,null,null,null,null
from category x1
where x1.catid=1
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now