SQL query to expand and flatten a hierarchy

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

Other Forums

Other Topics

SQL query to expand and flatten a hierarchy

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-13 : 08:23:26

steve writes "In our Oracle 9x database, I have:

SQL> select * from R_Rightsassociation;

Right.5 Right.3
Right.3 Right.4
Right.4 Right.2

I need a SQL query to give me a result set like (expand and flatten the lineage):

Right.5 Right.3
Right.5 Right.4
Right.5 Right.2
Right.3 Right.4
Right.3 Right.2
Right.4 Right.2

How would you suggest getting the desired result set? Thanks!"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-13 : 08:29:21

do you know how many steps... if so... that number of joins would probably do it.

otherwise... try reading: http://www.seventhnight.com/treestructs.asp

Its for MS Sql Server, but the concept still applies.

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-13 : 16:24:13

You want to look up the CONNECT BY operator

And pretty soon, SQL Express or 2005 with have that ability as well!!!!

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources