• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Get Path Enumeration From either Adjacency List or Nested Set Model

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
fischermx
Asked:
fischermx
1 Solution
 
Vadim RappCommented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now