Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I do ___________ with a Hierarchical structure?
T-SQL Hints and Tips
How!?!? Read up on the subject!! 
Links to other sites:
[link http://www.sqlteam.com/item.asp?ItemID=8866]More Trees & Hierarchies in SQL[/link]
[link http://www.intelligententerprise.com/001020/celko.jhtml]Trees in SQL[/link]
[link http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm]Versatile High Performance Hierarchies in SQL Server[/link]
[link http://www.dbazine.com/tropashko4.shtml]Trees in SQL: Nested Sets and Materialized Path[/link] (highly technical)
[link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asp]SQL Server Magazine January 2001: The Zen of Recursion[/link]
And a quick example on how to display a non-notated parent-child hierarchical structure without recursion:
Links to other sites:
[link http://www.sqlteam.com/item.asp?ItemID=8866]More Trees & Hierarchies in SQL[/link]
[link http://www.intelligententerprise.com/001020/celko.jhtml]Trees in SQL[/link]
[link http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm]Versatile High Performance Hierarchies in SQL Server[/link]
[link http://www.dbazine.com/tropashko4.shtml]Trees in SQL: Nested Sets and Materialized Path[/link] (highly technical)
[link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asp]SQL Server Magazine January 2001: The Zen of Recursion[/link]
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#