hierarchy - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

hierarchy

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-03-07 : 01:54:18

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 03:22:17

Read about Expanding Hierarchies in sql server help file

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-07 : 04:06:25

Hi,

Try with this it will work in Sql2005

DECLARE @Sample TABLE (ID INT, ParentID INT, Name VARCHAR(1000))

INSERT @Sample
SELECT 7, NULL, 'Ljunggren' UNION ALL
SELECT 3, 1, 'Gulli' UNION ALL
SELECT 8, 7, 'Kerstin' UNION ALL
SELECT 1, NULL, 'Rosberg' UNION ALL
SELECT 4, 2, 'Peter' UNION ALL
SELECT 5, 3, 'Susanne' UNION ALL
SELECT 2, 1, 'Jan-Eric' UNION ALL
SELECT 10, 9, 'Jennie' UNION ALL
SELECT 6, 3, 'Annelie' UNION ALL
SELECT 9, 7, 'Kenneth' UNION ALL
SELECT 11, 9, 'Jessica'

;WITH Yak (ID, ParentID, Name, Path, Indent)
AS (
SELECT ID, ParentID,Name, CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY Name)), 0
FROM @Sample
WHERE ParentID IS NULL

UNION ALL

SELECT s.ID,s.ParentID,s.Name, CONVERT(VARCHAR, y.Path + ',' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.Name DESC))),
y.Indent + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.ID = s.ParentID
)

SELECT y.ID,
y.ParentID,
REPLICATE('.....', Indent) + Name,
y.Path
FROM Yak y
--WHERE y.parentid = 7 (@parentId)
ORDER BY y.Path

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources