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 ?
LVL 1
fischermxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.