|
There is a very good reason Oracle implemented the CONNECT BY feature. That reason is that without it, doing what you want can be a royal pain. Unfortunately, SQL Server is lacking in its support for hierarchies. I see two ways to get around this. One option is to change how you store your hierarchy, such that each inherited relationship is stored. Take a look:
ID ParentID Level
-- -------- -----
5 4 1
5 3 2
5 2 3
5 1 4
4 3 1
4 2 2
4 1 3
3 2 1
3 1 2
2 1 1
The other option is to use some sort of WHILE loop in Transact-SQL to walk down the hierarchy and populate a temporary table. Ken Henderson's book, "The Guru's Guide to Transact-SQL," covers this in his chapter on hierarchies.
Neither one of these options is necessarily pretty, but both will work.
For More Information
- What do you think about this answer? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle, SQL Server, or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle, SQL Server, and SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
|
