Recursive Hierarchy flattening- SQL Syntax questio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive Hierarchy flattening- SQL Syntax questio

Author

Topic

singhmonica
Starting Member

22 Posts

Posted - 2005-01-21 : 17:35:34

"Below are my queries to flatten the hierarchies (Build from the codes I found on the internet)-
Function-
CREATE FUNCTION FindRoot1(@Al_Item_ID int)
RETURNS int
AS
BEGIN
DECLARE @Al_Father_ID int
SELECT @Al_Father_ID = Al_Father_ID
FROM td.all_lists
WHERE Al_Item_ID = @Al_Item_ID
WHILE @Al_Father_ID <> NULL
BEGIN
SELECT @Al_Item_ID = @Al_Father_ID
SELECT @Al_Father_ID = Al_Father_ID
FROM all_lists
WHERE Al_Item_ID = @Al_Item_ID
END
RETURN @Al_Item_ID
END
GO

Procedure-
CREATE PROCEDURE BuildTreeAllLists(@Al_Item_ID int)
AS
SET NOCOUNT ON
CREATE TABLE TEST_RESULTS( Level tinyint, Al_Item_ID int,
Al_Description varchar(255), Al_Father_ID int, Lineage varchar(255))
DECLARE @Al_Description varchar(255)
DECLARE @Al_Father_ID int
DECLARE @Level tinyint
DECLARE @Lineage varchar(255)
SELECT @level = 1
DECLARE @root int
SELECT @root = FindRoot1(@Al_Item_ID)
CREATE TABLE #stack (Al_Item_ID int, Level smallint)
INSERT INTO #stack VALUES (@root, @Level)
WHILE @Level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE Level = @Level)
BEGIN
SELECT @Al_Item_ID = s.Al_Item_ID, @Al_Description = t.Al_Description,
@Al_Father_ID = IsNull(t.Al_Father_ID, 0)
FROM #stack s INNER JOIN td.All_Lists t
ON t.Al_Item_ID = s.Al_Item_ID
WHERE Level = @Level
INSERT INTO TEST_RESULTS VALUES (@Level, @Al_Item_ID, @Al_Description,
@Al_Father_ID, @Lineage)
DELETE FROM #stack
WHERE Level = @Level
AND Al_Item_ID = @Al_Item_ID
AND Lineage = IsNull
INSERT #stack
SELECT Al_Item_ID, @Level + 1
FROM td.All_Lists
WHERE Al_Father_ID = @Al_Item_ID
IF @@ROWCOUNT >= 0
BEGIN
SELECT @Level = @Level + 1
END
END--IF EXISTS
ELSE
BEGIN
SELECT @Level = @Level - 1
END
END

I then run the following sql-
Alter table test_results
Add Node int NOT NULL IDENTITY (100,1)
UPDATE test_results SET Lineage='/', Level=0 WHERE Al_Father_ID = 0

I get the following table structure-
Level Al_Item_ID Descrp Al_Father_ID Lineage Node
0 2 Subject 0 / 100
NULL 664 ABC 2 NULL 101
NULL 2560 Data 664 NULL 102
NULL 2562 Promo 2560 NULL 103
NULL 2561 Profile 2560 NULL 104
NULL 2618 Custom 2561 NULL 105
NULL 2617 Volume 2561 NULL 106
NULL 2613 Address 2561 NULL 110

Questions-
1. How can I list the Lineage for the rest of the rows? Can I make it a part of the above procedure?-

Any help is much appreciated"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-24 : 17:19:13

Is this what you are trying to do:
http://www.seventhnight.com/treestructs.asp

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-01-26 : 12:57:08

Thanks seventhnight. I was able to modify your code and use it for my database.
I am now looking at page/part 2 of your code wherein you have a query for calculating a count of all the content items under a particular hierarchial level.
I have designed a PATHS table that has the 'path', 'al_item_id', and, 'al_father_id' columns.
I have another table called TEST which is my contents table in the database having the content item-testcase. The TEST table contains a 'ts_test_id' and 'ts_subject' (Foreign key of paths). The 'ts_subject' maps to the 'al_item_id' of the PATHS table.

My query below for the above design is giving me 0 count, although there are 38 distinct testcases with ts_subject=1348.

Declare @ts_subject int, @pad nvarchar(100)
Set @Pad = '0000'
Set @ts_subject = 1348

/*create table content (ts_test_Id int, ts_subject int)
Insert Into content
Select ts_test_id, ts_subject= ts_subject From td.test
*/

Select count(ts_test_id)
From td.test Z
Inner Join
(
Select ts_subject= al_item_Id
From paths as A
Where A.path like '%' + right(@pad + convert(nvarchar,@ts_subject),len(@pad))+';%'
and al_item_id <> @ts_subject
) Y
On Z.ts_subject = Y.ts_subject

could you help me to debug this?
thanks!

singhmonica
Starting Member

22 Posts

Posted - 2005-01-26 : 13:02:07

also, is there a way to modify what you have in the above query to calculate a count of content items(testcases) by specifying a hierarchial level, instead of specifying the ts_subject/content item id

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-26 : 16:11:08

Does the subquery (the part in bold), return the right number of records?


Select count(ts_test_id)
From td.test Z
Inner Join
(
Select ts_subject= al_item_Id
From paths as A
Where A.path like '%' + right(@pad + convert(nvarchar,@ts_subject),len(@pad))+';%'
and al_item_id <> @ts_subject
) Y
On Z.ts_subject = Y.ts_subject

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-26 : 16:23:48

Oh... and to look at the path in terms of levels, remember that the 'pad' is a fixed length, so the path increases in increments of len(@pad)+1:


Declare @paths table (path varchar(100), al_item_id int, al_father_id int)
Insert Into @paths
Select '0000;0001;', 1, 0
Union All Select '0000;0001;0002;', 2, 1
Union All Select '0000;0001;0002;0003', 3, 2
Union All Select '0000;0001;0002;0004', 4, 2
Union All Select '0000;0001;0005;', 5, 1
Union All Select '0000;0001;0006;', 6, 1
Union All Select '0000;0007;', 7, 0
Union All Select '0000;0007;0008;', 8, 7
Union All Select '0000;0007;0008;0009;', 9, 8
Union All Select '0000;0007;0008;0009;0010;', 10, 9


Declare @getLevelCnt int,
	@pad varchar(100)
Set @pad = '0000'
Set @getLevelCnt = 2

Select * From @paths Where len(path) = @getLevelCnt*(len(@pad)+1)

Tweak to your purpose of course.

And if you are still having problems with the debugging, send me some test data that is not working for you...

Good Luck !

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-01-26 : 17:35:37

Thanks Corey. Your code does work! I am adding more complexities to the query. Would like to send you my data and queries. How can I send you an attachment?

singhmonica
Starting Member

22 Posts

Posted - 2005-01-26 : 20:28:06

I have an ALL_LISTS table that stores the parent-child relationships having columns al_father_id, al_Item_ID .

The TEST table stores Testcase information. The TEST table has ts_test_id and ts_subject columns. Ts_subject is a foreign key of the all_lists table. The mapping is as follows-
test.ts_subject = all_lists.al_item_ID.

In our database, the all_lists table stores any kind of parent child relationship. So it will have more information then just the test case hierarchies. We limit the information we want to see by specifying the al_item_id=2, which is the SUBJECT- the ultimate parent for the hierarchies we are concerned about. A subject can have multiple projects, tracks, phases, level x, level y, and so on. The test case can be at any level in the hierarchy. A test case can have only one immediate parent i.e. there are no multiple parents. (Just like the files and folders structure of Microsoft windows explorer where content item/testcase is the file, and the folders are the hierarchies).

I first create a Test_Results table that basically stores information for all the rows of the ALL_LISTS table that have ultimate parent of Subject (al_father_id=0). The test_results table lists the hierarchy levels. The test_results calls a function FindRoot1. Below is the syntax-
CREATE FUNCTION FindRoot1(@Al_Item_ID int)
RETURNS int
AS
BEGIN
DECLARE @Al_Father_ID int
SELECT @Al_Father_ID = Al_Father_ID
FROM td.all_lists
WHERE Al_Item_ID = @Al_Item_ID
WHILE @Al_Father_ID <> NULL
BEGIN
SELECT @Al_Item_ID = @Al_Father_ID
SELECT @Al_Father_ID = Al_Father_ID
FROM td.all_lists
WHERE Al_Item_ID = @Al_Item_ID
END
RETURN @Al_Item_ID
END

Script for test_results-
ALTER PROCEDURE BuildTest_ResultsAllLists(@Al_Item_ID int)
AS
SET NOCOUNT ON
CREATE TABLE TEST_RESULTS( Level tinyint, Al_Item_ID int,
Al_Description varchar(255), Al_Father_ID int, Processed bit default(0))
DECLARE @Al_Description varchar(255)
DECLARE @Al_Father_ID int
DECLARE @Processed bit
DECLARE @Level tinyint
SELECT @level = 1
DECLARE @root int
SELECT @root = ms.FindRoot1(@Al_Item_ID)
CREATE TABLE #stack (Al_Item_ID int, Level smallint)
INSERT INTO #stack VALUES (@root, @Level)
WHILE @Level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE Level = @Level)
BEGIN
SELECT @Al_Item_ID = s.Al_Item_ID, @Al_Description = t.Al_Description,
@Al_Father_ID = IsNull(t.Al_Father_ID, 0),
@Processed = 0
FROM #stack s INNER JOIN td.All_Lists t
ON t.Al_Item_ID = s.Al_Item_ID
WHERE Level = @Level
INSERT INTO TEST_RESULTS VALUES ( @Level, @Al_Item_ID, @Al_Description,
@Al_Father_ID, @Processed)
DELETE FROM #stack
WHERE Level = @Level
AND Al_Item_ID = @Al_Item_ID

INSERT #stack
SELECT Al_Item_ID, @Level + 1
FROM td.All_Lists
WHERE Al_Father_ID = @Al_Item_ID
IF @@ROWCOUNT >= 0
BEGIN
SELECT @Level = @Level + 1
END
END--IF EXISTS
ELSE
BEGIN
SELECT @Level = @Level - 1
END
END -- WHILE

I then create a paths table –
Declare @pad nvarchar(100),
@lastCnt int
Set @Pad = '0000'
Create table paths (Level tinyint, path nvarchar(1000), Al_Father_ID int, Al_Item_ID int )
Insert Into paths
Select
Level,
path=right(@pad + convert(nvarchar,Al_Father_ID),len(@pad))+'/' + right(@pad + convert(nvarchar,Al_Item_ID),len(@pad))+'/',
Al_Father_ID,
Al_Item_ID
From Test_Results where Al_Father_ID=0
Update A
Set Processed = 1
From Test_Results as A
Inner Join paths as B
On A.Al_Father_ID = B.Al_Father_ID
and A.Al_Item_ID = B.Al_Item_ID

While exists(Select * From Test_Results Where Processed = 0) Begin
Insert Into paths
Select
B.Level,
path=path + case when B.Al_Item_ID is not null then right(@pad+convert(nvarchar,B.Al_Item_ID),len(@pad))+'/' else '' end,
B.Al_Father_ID,
B.Al_Item_ID
From Paths as A
Left Join Test_Results as B
On A.Al_Item_ID = B.Al_Father_ID
Where B.Processed = 0
Update A
Set Processed = 1
From Test_Results as A
Inner Join paths as B
On A.Al_Father_ID = B.Al_Father_ID
and A.Al_Item_ID = B.Al_Item_ID
Where A.Processed = 0
End

This also updates the test_results table, and sets processed =1.

Now, the query below lists every single al_item_ids at the specified level, while I want to list only those al_item_ids that have a corresponding value in the test table. This is where I am having the most problem as to how to develop the code linking the paths and test tables and getting a count/listing of Test cases at a level (not a listing/count of hierarchies at that level)

Declare @getLevelCnt int,
@pad varchar(100)
Set @pad = '0000'
Set @getLevelCnt = 12

Select * From paths Where len(path) = @getLevelCnt*(len(@pad)+1)

Sample Data-
At this point this is what the query above lists out where level=11. It is exactly the same output one gets from paths table for level specified as = 11.

level path al_father_id al_Item_ID
11 0000/0002/0664/1232/0665/0666/1335/1167/1171/1203/1227/1496/ 1227 1496
11 0000/0002/0664/1232/0665/0666/1335/1167/1173/1202/1353/1348/ 1353 1348
11 0000/0002/0664/1232/0665/0666/1335/1167/1173/1202/1353/1346/ 1353 1346
11 0000/0002/0664/1233/1243/1573/1572/1583/1584/1585/1587/1589/ 1587 1589
11 0000/0002/0664/1233/1243/1573/1572/1583/1584/1585/1587/1588/ 1587 1588

Test table sample data -
There are no testcases for al_Item_ID 1496, 1588, 1589, so they should not be listed.
The following is the list of testcases from test table for the ts_subject 1346 and 1348-
ts_test_id ts_subject
214 1346
215 1346
216 1346
6822 1346
6823 1346
6824 1346
199 1348
200 1348
201 1348
202 1348
203 1348
204 1348
205 1348
285 1348
287 1348
288 1348
299 1348
300 1348
301 1348
302 1348
303 1348
304 1348
305 1348
306 1348
307 1348
501 1348
502 1348
6252 1348

so my query should yeild something like this-
level path al_father_id al_Item_ID #of TC
11 0000/0002/0664/1232/0665/0666/1335/1167/1173/1202/1353/1348/ 1353 1348 21
11 0000/0002/0664/1232/0665/0666/1335/1167/1173/1202/1353/1346/ 1353 1346 6

1. How do I link the paths and test tables to select count of only those records from paths that have a corresponding row in test table?
2. How do I check to see the count of testcases for a given ts_subject in the test table?
3. I want to accomodate all the above code in one single stored procedure. Also at the end have drop statements for paths, test_results and any other additional table- ONLY before data loads, so that it will pick any changes to testcase information (deletes, changes in hierarchy). However, the test_results and paths table should exist otherwise for me to write reports on.

singhmonica
Starting Member

22 Posts

Posted - 2005-01-26 : 20:32:16

For Q2, pls. ignore for now. Its a simple count(*) :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-26 : 21:06:26

How do I link the paths and test tables to select count of only those records from paths that have a corresponding row in test table?

It seems like you just need to join your test case table to the result of the paths filter:


Declare @getLevelCnt int,
	@pad varchar(100)

Set @pad = '0000'
Set @getLevelCnt = 12

Select * 
From
	(
	Select * From paths Where len(path) = @getLevelCnt*(len(@pad)+1)
	) FilteredPaths
Inner Join test TestCases
On FilteredPaths.al_Item_ID = TestCases.ts_Subject

--To also get the count in one shot

Select
	FilteredPaths.level,
	FilteredPaths.path,
	FilteredPaths.al_father_id,
	FilteredPaths.al_Item_ID,
	TestCases.Cnt
From
	(
	Select * From paths Where len(path) = @getLevelCnt*(len(@pad)+1)
	) FilteredPaths
Inner Join (Select ts_Subject, cnt = count(*) From test Group By ts_Subject) TestCases
On FilteredPaths.al_Item_ID = TestCases.ts_Subject

Now, I'm not sure what you want the stored procedure, as I am kinda lost on the first couple bits of code. It really is a matter of just working with it all in pieces, and then just dropping the pieces into a single sproc. (in the right order of course!)

If you have something you want to attach you can email me through sqlteam, and I'll respond from my work address.

Good Luck!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-01-27 : 19:01:23

Thanks Corey! I need your help again- this time to modify the above code to list/count all the testcases underneath and upto a certain level i.e. list/count of testcases from level 11 to the last hierarchy if the specified level is 11. Right now the query lists cases at level 11, and not anything at levels 12,13,...
This is what I have attempted (with my limited sql knowledge)
Declare @getLevelCnt int,
@pad varchar(100)

Set @pad = '0000'
/*Set @getLevelCnt = 12*/
While exists (Select * from paths where @getLevelCnt >=12)
Select
FilteredPaths.level,
FilteredPaths.path,
FilteredPaths.al_father_id,
FilteredPaths.al_Item_ID,
FilteredPaths.al_description,
TestCases.Cnt
From
(
Select * From paths Where len(path) = @getLevelCnt*(len(@pad)+1)
) FilteredPaths
Inner Join (Select ts_Subject, cnt = count(*) From td.test Group By ts_Subject) TestCases
On FilteredPaths.al_Item_ID = TestCases.ts_Subject

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-27 : 21:47:02

it seems like you would just do (notice the only change is in blue):


Select
	FilteredPaths.level,
	FilteredPaths.path,
	FilteredPaths.al_father_id,
	FilteredPaths.al_Item_ID,
	TestCases.Cnt
From
	(
	Select * From paths Where len(path) >= @getLevelCnt*(len(@pad)+1)
	) FilteredPaths
Inner Join (Select ts_Subject, cnt = count(*) From test Group By ts_Subject) TestCases
On FilteredPaths.al_Item_ID = TestCases.ts_Subject

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-01-31 : 12:12:06

Thanks Corey!
The query works!

singhmonica
Starting Member

22 Posts

Posted - 2005-02-03 : 13:56:43

Hi Corey,
I have another question on the same Paths and Test tables-
The Paths table contains information on the path and level. The output for

path is a single row- 0000/0002/180/...
The Test table has Test Cases information.

I want to create a new table Test_detail that has all the columns from the

Test table, and the level and Path information from the Paths table. The

path information should be in a way, that each node is added as a column to

the table. The output should look like-
Table Test_detail-
Ts_test_id, (from test table)
Ts_subjecy, (from test table)
Ts_name, (from test table)
Path1 (from paths table)
Path2 (from paths table)
Pathn (from paths table)

Output-
ts_test_id Ts_subject ts_name path1 path2 path3 path4 pathn
1275 3209 Testing 0 180 NULL NULL NULL
1345 256 BTD 0 178 3456 2345 7896
2345 567 Email 0 178 3456 NULL NULL

How do I approach this?
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 15:05:20

Not sure about your entire question.... but here is at least some of it:


Declare @pad varchar(100)
Set @pad = '0000'

Declare @myTable table (path varchar(100))
Insert Into @myTable
Select '0000;0002;0180;1234;5246;'
Union Select '0000;0002;0181;'
Union Select '0000;0002;0184;5421;'

Select 
	path, 
	path1 = nullif(Substring(path,1+((len(@pad)+1)*0),4),''),
	path2 = nullif(Substring(path,1+((len(@pad)+1)*1),4),''),
	path3 = nullif(Substring(path,1+((len(@pad)+1)*2),4),''),
	path4 = nullif(Substring(path,1+((len(@pad)+1)*3),4),''),
	path5 = nullif(Substring(path,1+((len(@pad)+1)*4),4),'')
From @myTable

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-02-03 : 17:51:50

Hi Corey,
Some more details about what I am trying to do-
My current database has recursive parent-child relationships, which is not effective for reporting. I am designing tables that flatten out the relationships so I can report on various hierarchies.

The front end is Business Objects which lets create custom hiearchy to drill down on. To be able to drill on the various nodes in the hierarchy, each node should be a seperate table or column in the database.

I am developing a database design that has all the hierarchy information listed out so it can be passed in one step. The lowest flag should be where paths.al_item_id=test.ts_subject. Where this condition is met, it is a test case (content item).

Paths table- has path and level information for all the hierarchies.
Test table- has test case detail. test.ts_subject = paths.al_item_id.

Test_Detail table structure-
Test_id, test_name, ts_subject, path1, path2.... pathn
The logic for determining which of the

test_id ts_sub al_item_id path1 path2 path3 path4 path5 path6 path7
15 180 180 0 2 null null null null null
48 1218 1218 0 2 664 1232 665 666 1335
91 1231 1231 0 2 664 1232 665 666 1335
77 2804 2804 0 2 664 256 251 253 null
81 2804 2804 0 2 664 256 251 253 null

Count of tests at P6= 4
count of tests at p7= 2
count of tests at p2= 5

I want to write a script that automates the creation of the Test_Detail table? I am open to any discussion the DB/table(s) design as well?
Any help is much appreciated.

singhmonica
Starting Member

22 Posts

Posted - 2005-02-03 : 17:54:47

The paths table structure is-
Level, path, al_father_id, Al_item_id

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 18:09:09

quote:

My current database has recursive parent-child relationships, which is not effective for reporting. I am designing tables that flatten out the relationships so I can report on various hierarchies.

The front end is Business Objects which lets create custom hiearchy to drill down on. To be able to drill on the various nodes in the hierarchy, each node should be a seperate table or column in the database.

I am pretty sure that I disagree with the red portion(s) of your statement. The first thing you are doing is taking a recursive set of relationships and condensing them into a singular path from the top for each node. This can open up a great deal of information, and can make it simple to show tree-like reporting from a single page.

However, it seems that if you are going to use a drill down method of reporting, then you may not need to build the paths table at all. Why would you not simply drill down through the recursive relationships. At each step there are child nodes. These child nodes may also have (other) child nodes, or the child nodes may be test cases. Either way the path to the node is not particularly important as you are viewing isolated sections of a tree.

Secondly, there would be no need to split the path into multiple columns. You can use the LIKE keyword to show any children of a given node.

for example:
...
where path like '%0180;____;'

could return '...;0180;2134;'
but not '...;0180;' or '...;0180;2134;2415;'

I hope this helps demonstrate some of your options.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-02-03 : 20:06:01

Corey,
The reason I need the path information is because Business Objects tool retrieves all of the rows for a drillable report at once. Since it does not requery the database as you drill, you need to be able to obtain all of the data that you need in one pass.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 20:12:17

So the 'Business Objects' tool requires the multi columns to allow it to drill? I have built several reports that drill with just the path column and the detail data.

What are you working in? ASP? .NET?

Anyway... build a good sample from start to finish... Indicate what you want the final outcome to be. And I'll load it up and see what I can do.

oh... and if you use [code] tags, you can align your columns and they will stay straight when you post

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

singhmonica
Starting Member

22 Posts

Posted - 2005-02-09 : 18:42:50

Hi Corey,
I have sent you some attachments on your email. Let me know if its doable.
Thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources