Tree structure of parent child

Question

Tree structure of parent child

S-322532

SSCommitted

Points: 1613
More actions
March 15, 2016 at 9:25 pm

#149270

Two tables are defined below. Names are arranged in a parent-child relationship. How to show a nested (tree) list of names including [Id], [Name] and [Level], where [Level] indicates the nest level from the top (Root: Level = 0; First children of Root: Level = 1; etc…).
CREATE TABLE [Names]
(
[Id] INT PRIMARY KEY,
[Name] VARCHAR(100)
)
CREATE TABLE [Relationships]
(
[Child] [int] REFERENCES [Names]([Id]),
[Parent] [int] REFERENCES [Names]([Id])
)
Sample Data:
INSERT [NAMES] VALUES (1,'Cat')
INSERT [NAMES] VALUES (2,'Leopard')
INSERT [NAMES] VALUES (3,'White Leopard')
INSERT [NAMES] VALUES (4,'Tiger')
INSERT [NAMES] VALUES (5,'Yellow Leopard')
INSERT [RELATIONSHIPS] VALUES (1,NULL)
INSERT [RELATIONSHIPS] VALUES (2,1)
INSERT [RELATIONSHIPS] VALUES (3,2)
INSERT [RELATIONSHIPS] VALUES (4,1)
INSERT [RELATIONSHIPS] VALUES (5,2)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Using your data, pick what you need from the following. Do not get rid of the SortPath in the CTE. You can get rid of it in the outer select but it must be included in the ORDER BY to come out right for the nested groupings.

WITH cteHierarchy AS

( --=== Select the root node first, like you would in a loop

SELECT Child

,Parent

,hLevel = 1

,SortPath = CAST(CAST(Child AS BINARY(4)) AS VARBINARY(1000))

FROM dbo.Relationships

WHERE Parent IS NULL

UNION ALL --Then recurse through each level of the hierarchy and build up the sort path.

SELECT tbl.Child

,tbl.Parent

,hLevel = cte.hLevel+1

,SortPath = CAST(cte.SortPath + CAST(tbl.Child AS BINARY(4)) AS VARBINARY(1000))

FROM dbo.Relationships tbl

JOIN cteHierarchy cte

ON tbl.Parent = cte.Child

) --=== Do joins to get the names of the Child and Parent alson with final formatting.

SELECT hcy.Child

,hcy.Parent

,ChildName = SPACE((hLevel-1)*2)+namc.Name --Indented Names

,ParentName = SPACE((hLevel-1)*2)+namp.Name --Indented Names

,hLevel

,SortPath

FROM cteHierarchy hcy

LEFT JOIN dbo.Names namc ON namc.Id = hcy.Child

LEFT JOIN dbo.Names namp ON namp.Id = hcy.Parent

ORDER BY hcy.SortPath

;

Results from above:

Child Parent ChildName ParentName hLevel SortPath

----- ------ ------------------ ----------- ------ --------------------------

1 NULL Cat NULL 1 0x00000001

2 1 Leopard Cat 2 0x0000000100000002

3 2 White Leopard Leopard 3 0x000000010000000200000003

5 2 Yellow Leopard Leopard 3 0x000000010000000200000005

4 1 Tiger Cat 2 0x0000000100000004

(5 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S-322532 SSCommitted Points: 1613 More actions · Answer 2

Dear Jeff,

Sorry for the late response, the solution you provided worked great. Thanks a lot :-):-)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

You're welcome. Thank you for the feedback. The question now is... do you understand it so that you can maintain it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)