Contact US

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.

Students Click Here

Microsoft SQL Server: Programming FAQ

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:


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#

      Hierarchy =
            + 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),')
      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

      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


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

My Archive

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