Relational to heirarchical - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Relational to heirarchical

Author

Topic

sdseal
Starting Member

9 Posts

Posted - 2011-04-02 : 22:51:33

I have inherited an older database structure that had heirarchical data stored in relational tables.

Basically it goes something like this:

Level 1 table (root) fields:
CompID (primary key)
Comp (nvarchar)

Level 2 table fields:
ObjID (primary key)
Obj (nvarchar)
CompID (foreign key)

Level 3 table fields:
SubObID (primary key)
SubOb (nvarchar)
ObjID (foreign key)

Need a SPROC which returns a table in a heirarchical structure:

ID(int) Info (nvarchar) Parent(int)

I'm certainly not a great T-Sql guy. I've searched for days but can't come up with one good answer.

Thanks for any effort on this.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-03 : 02:58:56

hi,

what is the result of your query? please provide some sample data and a sample output.

thank you

sdseal
Starting Member

9 Posts

Posted - 2011-04-03 : 10:03:12

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-03 : 10:42:31

Based on the sample data, you cannot tell which level an object belongs to examining only its Id. If you could do that, life would be much easier.

So one approach would be to make the Id spaces for the 3 levels disjoint. For example, you might say that all level 1 Id's are prefixed with a "C", level 2 with a "O" and level 3 with a "S". Or, you could specify that Ids 1-1000 are for Level 1, 1001 to 10000 for Level 2 and 10001 and above for Level 3. If you can do that, all you would then need to do is a union of all three tables.

select * from Level1Table
union all
select * from Level2Table
union all
select * from Level3Table

If you are allowed to do this, two or 3 update statements can move the existing Id's into their on spaces. You will also need to make sure that other clients who insert the data into the tables will follow these new rules.

If you have a lot of clients who won't follow your new rules, another possibility is to add another column to each of the tables which would hold the Id's as I described above. You can make these new columns identity columns - which would result in minimal impact to existing clients who are trying to insert or read from these tables. But your query to get the hierarchy information will be slightly more complex than the simple "union all".

If neither of this is a possibility, it can still be done, but you will have to subject yourself to more agony and a higher likelihood of error.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-03 : 11:48:40

SDSEAL: how do you determine the parent? i'm still confused with your data sample and data output.

sdseal
Starting Member

9 Posts

Posted - 2011-04-03 : 20:33:27

sunitabeck,

I appreciate the suggestions. That's pretty much what I'm doing in the .net side currently. I bring in all 3 tables and the iterate through each and build the final (hierachical) table. I bring in a label for each table (I use comp for level1, obj for level2 and sub for level3). I figure that SQL would be faster and more efficient that .net. Plus, it'd move code out of .net and make it simpler to read and understand.

slimt_slimt,
The data structure for the relational tables are pretty straight forward as outlined in my first post. The sample data in the second post is just fills it out. I'm just trying to convert the relational structure and return one table of hierarchical data. The ID in the hierarchical table would just be incremented. The data field in the hierarchical table would hold the data from the table. The parent field would identify the ID (from the hierarchical table) that the child(node) belongs to.

If you look at the desired query output table, record 2's data is a child of the same table's record 1. Record 4 is also a child of record 1. Record 1's has no parent so it's parent would be 0 or null (I can use either, just need the query to make it one or the other consistently.

For an online example, check this wikipedia (sorry guys/gals) entry on the subject: http://en.wikipedia.org/wiki/Hierarchical_database_model

................

To all, I certainly don't mind my current database's structure of using the related tables to store this data. It's easier to maintain. But converting it into an adjacency list model is simple in concept but I'm lacking the skills to accomplish this in SQL.

Thanks for the input and patience as I learn.

[url][/url][url][/url]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 08:04:36

Do you expect IDs in the desired output table to be repeatable? Either they can be repeatable or they can be consecutive. What I mean is, if I were to insert a new row into the sub-table as follows:

4 | Some SubOb Data 4 | 1

In the desired output, would you expect many of the rows to get pushed down? For example is it acceptable that

5 | Some Comp Data 2 | 0 (or null)

would then become

6 | Some Comp Data 2 | 0 (or null)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 08:23:52

This should work if it is just like you described... but if you add more layers, it will become messier.


Declare @t1 table (
	id1 int,
	d1 varchar(100)
)
Insert Into @t1 Select 1, 'Some Comp Data 1'
Insert Into @t1 Select 2, 'Some Comp Data 2'

Declare @t2 table (
	id2 int, 
	d2 varchar(100), 
	id1 int
)
Insert Into @t2 Select 1, 'Some Obj Data 1', 1
Insert Into @t2 Select 2, 'Some Obj Data 2', 1
Insert Into @t2 Select 3, 'Some Obj Data 3', 2
Insert Into @t2 Select 4, 'Some Obj Data 4', 2

Declare @t3 table (
	id3 int,
	d3 varchar(100),
	id2 int
)
Insert Into @t3 Select 1, 'Some SubOb Data 1', 1
Insert Into @t3 Select 2, 'Some SubOb Data 2', 3
Insert Into @t3 Select 3, 'Some SubOb Data 3', 3


Select 
	Row_Number()Over(Order By id),
	* 
From
	(
	Select 
		id = id1*100000,
		data = d1
	From @t1 
	Union 
	Select 
		id = id1*100000 + id2 * 1000,
		data = d2
	From @t2
	Union 
	Select 
		id = B.id1*100000 + A.id2 * 1000 + id3,
		data = d3
	From @t3 A
	Inner Join @t2 B
	On A.id2 = B.id2
	) Z
Order By id

Corey

I Has Returned!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-04 : 09:03:04

Or... you could normalize your data on the fly and use a recursive cte?

-- Normalize the source data!
;WITH cteSource(ID, Descr, Parent, Lvl)
AS (
	SELECT	ID1 AS ID,
		d1 AS Descr,
		NULL AS Parent,
		1 AS Lvl
	FROM	@t1

	UNION ALL

	SELECT	ID2 AS ID,
		d2 AS Descr,
		ID1 AS Parent,
		2 AS Lvl
	FROM	@t2

	UNION ALL

	SELECT	ID3 AS ID,
		d3 AS Descr,
		ID2 AS Parent,
		3 AS Lvl
	FROM	@t3
), cteTree(ID, Descr, Parent, Lvl, TreePath)
-- Create a normal recursive query
AS (
	SELECT	ID,
		Descr,
		Parent,
		Lvl,
		'\' + CAST(ID AS VARCHAR(MAX)) + '\' AS TreePath
	FROM	cteSource
	WHERE	Lvl = 1

	UNION ALL

	SELECT		s.ID,
			s.Descr,
			s.Parent,
			s.Lvl,
			t.TreePath + CAST(s.ID AS VARCHAR(MAX)) + '\' AS TreePath
	FROM		cteTree AS t
	INNER JOIN	cteSource AS s ON s.Parent = t.ID
				AND s.Lvl = t.Lvl + 1
)
-- Display the wanted result
SELECT		ROW_NUMBER() OVER (ORDER BY TreePath) AS ID,
		Descr AS Data,
		Parent
FROM		cteTree
ORDER BY	TreePath

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 09:10:45

The recursive CTE output seems to retain the original parent id. May be something more needs to be done to assign the correct parent to each child and grandchild.

sdseal
Starting Member

9 Posts

Posted - 2011-04-04 : 18:52:34

You folks are great!!

sunitabeck:
Since the code already exist to maintain the data in relational tables, I'm not planning on moving away from the current relational tables. The hierarchical table (in my application) would simply be used for display purposes... a temp table would suffice. No insert/update will be done to the data. Users will be able to check or uncheck the nodes and I'm handling that in JQuery.

PESO:
I'm REALLY unfamiliar with recursive CTE. If that's the way I need to go, I'll jump in and start trying to figure it out. I does look clean and much more elegant than what I'm currently doing.

Seventhnight:
That looks like something I was thinking about, but in reality there are 5 layers/levels (5 relational tables) deep. So it really DOES get messy.

Thanks. If any other ideas please let me know.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 19:19:57

If you are only looking for an ordered list, Peso's recursive query would be the best, especially if you want to go several layers deep. If you run it, you will see that it correctly orders the data. But the third column - parent- is not calculated correctly. If you do need the parent column, that can be fixed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources