Solved

Get Path Enumeration From either Adjacency List or Nested Set Model

Posted on 2007-04-01
1
471 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 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