Solved

Get Path Enumeration From either Adjacency List or Nested Set Model

Posted on 2007-04-01
1
472 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
ID: 18832143
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 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