INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

How do I do ___________ with a Hierarchical structure? by ESquared
Posted: 23 Jul 04 (Edited 19 Nov 04)

How!?!? Read up on the subject!!

Links to other sites:

More Trees & Hierarchies in SQL
Trees in SQL
Versatile High Performance Hierarchies in SQL Server
Trees in SQL: Nested Sets and Materialized Path (highly technical)
SQL Server Magazine January 2001: The Zen of Recursion


And a quick example on how to display a non-notated parent-child hierarchical structure without recursion:

CODE

create table hier# (
   code int,
   parent int)

insert into hier#
   SELECT 18000, 12000 UNION
   SELECT 18001, 18000 UNION
   SELECT 18002, 18000 UNION
   SELECT 18003, 18000 UNION
   SELECT 18004, 18000 UNION
   SELECT 18009, 18000 UNION
   SELECT 18005, 18004 UNION
   SELECT 18006, 18004 UNION
   SELECT 18007, 18004 UNION
   SELECT 18008, 18004 UNION
   SELECT 18010, 18009 UNION
   SELECT 18011, 18009 UNION
   SELECT 18012, 18011 UNION
   SELECT 12000, Null

select * from hier#

SELECT
      Hierarchy =
         SUBSTRING(
            ISNULL('/'+Convert(varchar,A1.Code),')
            + ISNULL('/'+Convert(varchar,A2.Code),')
            + ISNULL('/'+Convert(varchar,A3.Code),')
            + ISNULL('/'+Convert(varchar,A4.Code),')
            + ISNULL('/'+Convert(varchar,A5.Code),')
            + ISNULL('/'+Convert(varchar,A6.Code),')
            + ISNULL('/'+Convert(varchar,A7.Code),')
            + ISNULL('/'+Convert(varchar,A8.Code),')
         ,2,8000),
      L1 = A1.Code,
      L2 = A2.Code,
      L3 = A3.Code,
      L4 = A4.Code,
      L5 = A5.Code,
      L6 = A6.Code,
      L7 = A7.Code,
      L8 = A8.Code
   FROM hier# A1
      LEFT JOIN hier# A2 ON A1.Code = A2.Parent
      LEFT JOIN hier# A3 ON A2.Code = A3.Parent
      LEFT JOIN hier# A4 ON A3.Code = A4.Parent
      LEFT JOIN hier# A5 ON A4.Code = A5.Parent
      LEFT JOIN hier# A6 ON A5.Code = A6.Parent
      LEFT JOIN hier# A7 ON A6.Code = A7.Parent
      LEFT JOIN hier# A8 ON A7.Code = A8.Parent
      LEFT JOIN hier# A9 ON A8.Code = A9.Parent
   WHERE A1.Code = 12000

SELECT
      L1 = A6.Code,
      L2 = A5.Code,
      L3 = A4.Code,
      L4 = A3.Code,
      L5 = A2.Code,
      L6 = A1.Code

   FROM hier# A6
      LEFT JOIN hier# A5 ON A6.Parent = A5.Code
      LEFT JOIN hier# A4 ON A5.Parent = A4.Code
      LEFT JOIN hier# A3 ON A4.Parent = A3.Code
      LEFT JOIN hier# A2 ON A3.Parent = A2.Code
      LEFT JOIN hier# A1 ON A2.Parent = A1.Code

DROP TABLE hier#

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close