query tree, show tree structure
-
Hi,
i have a tree, let's say like the following
A
-Aa
--Aa1
--Aa2
B
-Bb
--Bb1
--Bb2
I would like to query the tree and show the output similar as avove.
I managed to query the tree using cte, but the order of the tree was lost. the output was like:
A
B
Aa
Bb
..
Can one give an example?
Thanks
Tobias
-
Plase post table structure, sample data (as insert statements) and desired output.
See - http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You should be able to track depth easily with yoru CTE (if you are not already). Use this depth to append a precursor string... such as REPLICATE('-', Depth) + Label
-
MentalWhiteNoise (7/9/2008)
You should be able to track depth easily with yoru CTE (if you are not already). Use this depth to append a precursor string... such as REPLICATE('-', Depth) + LabelCool... have you actually got any code to show how to do that?
--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) -
Jeff Moden (7/9/2008)
MentalWhiteNoise (7/9/2008)
You should be able to track depth easily with yoru CTE (if you are not already). Use this depth to append a precursor string... such as REPLICATE('-', Depth) + LabelCool... have you actually got any code to show how to do that?
Sure. Please feel free to provide any suggestions, but just giving it a quick go, here is some code:
SELECT *INTO #tmpTable
FROM (
SELECT 1 AS [ID], 'A' AS Label, NULL AS ParentID
UNION
SELECT 2, 'Aa', 1
UNION
SELECT 3, 'Aa1', 2
UNION
SELECT 4, 'Aa2', 2
UNION
SELECT 5, 'B', NULL
UNION
SELECT 6, 'Bb', 5
UNION
SELECT 7, 'Bb1', 6
UNION
SELECT 8, 'Bb2', 6
) A
; WITH TempTable AS
(SELECT ID, 0 AS Depth, Label, ParentID FROM #tmpTable WHERE ParentID IS NULL
UNION ALL
SELECT A.ID, B.Depth + 1, A.Label, A.ParentID FROM #tmpTable A JOIN TempTable B ON A.ParentID = B.ID)
SELECT REPLICATE('-', Depth) + Label FROM TempTable ORDER BY ID
-
Nicely done! Thanks! 🙂
--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) -
The neat thing about that code is that it allows for multiple top levels if you need them... thanks again.
--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) -
Aw rats... the fly in the ointment is that the ID's must already be in the correct hierarchical order in order to display in the correct order... Gotta add something different on the code to ORDER BY...
--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) -
Jeff Moden (7/9/2008)
Aw rats... the fly in the ointment is that the ID's must already be in the correct hierarchical order in order to display in the correct order... Gotta add something different on the code to ORDER BY...You need to order by a materialised path
; WITH TempTable AS(SELECT ID, 0 AS Depth, Label, ParentID, CAST(Label AS VARCHAR(MAX)) AS FullPath FROM #tmpTable WHERE ParentID IS NULL
UNION ALL
SELECT A.ID, B.Depth + 1, A.Label, A.ParentID, B.FullPath + '\' + CAST(A.Label AS VARCHAR(MAX)) FROM #tmpTable A JOIN TempTable B ON A.ParentID = B.ID)
SELECT REPLICATE('-', Depth) + Label
FROM TempTable
ORDER BY FullPath
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ah. Thanks Mark! Good catch, Jeff. The way I set up my quick table I didn't notice that.
Taking some time to play around with the hierarchy (taking the que from Mark's FullPath query), I have created a full "tree" like structure:
|-A| \-Aa
| |-Aa1
| | |-Aa1-1
| | \-Aa1-2
| \-Aa2
\-B
\-Bb
|-Bb1
|-Bb2
\-Bb3
DROP TABLE #tmpTableCREATE TABLE #tmpTable ([ID] INT, Label VARCHAR(25), ParentID INT, SortOrder INT)
INSERT INTO #tmpTable
SELECT *
FROM (
SELECT 1 AS [ID], 'A' AS Label, NULL AS ParentID, 1 AS SortOrder
UNION
SELECT 2, 'Aa', 1, 1
UNION
SELECT 3, 'Aa1', 2, 1
UNION
SELECT 10, 'Aa1-1', 3, 1
UNION
SELECT 11, 'Aa1-2', 3, 2
UNION
SELECT 4, 'Aa2', 2, 2
UNION
SELECT 5, 'B', NULL, 2
UNION
SELECT 6, 'Bb', 5, 1
UNION
SELECT 7, 'Bb1', 6, 1
UNION
SELECT 8, 'Bb2', 6, 2
UNION
SELECT 9, 'Bb3', 6, 3
) A
SELECT *
, ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY CAST(SortOrder AS VARBINARY(MAX))) AS Row
, (SELECT COUNT(*) FROM #tmpTable B WHERE ISNULL(B.ParentID,-1) = ISNULL(A.ParentID,-1)) AS SiblingCount
INTO #tmpTable2
FROM #tmpTable A
; WITH TempTable AS
(
SELECT ID
, 0 AS Depth
, Label
, ParentID
, CAST(SortOrder AS VARBINARY(MAX)) AS FullPath
, CAST(CASE WHEN Row = SiblingCount THEN '\-' ELSE '|-' END AS VARCHAR(MAX)) AS TextPath
, Row
, SiblingCount
FROM #tmpTable2
WHERE ParentID IS NULL
UNION ALL
SELECT A.ID
, B.Depth + 1
, A.Label
, A.ParentID
, B.FullPath + CAST(A.SortOrder AS VARBINARY(MAX))
, CASE B.TextPath
WHEN '' THEN ''
ELSE ISNULL(LEFT(B.TextPath, LEN(B.TextPath) - 2), '')
END +
CASE WHEN B.Row = B.SiblingCount THEN ' ' ELSE '| ' END +
CASE WHEN A.Row = A.SiblingCount THEN '\-' ELSE '|-' END
, A.Row
, A.SiblingCount
FROM #tmpTable2 A
JOIN TempTable B
ON A.ParentID = B.ID
)
SELECT TextPath + Label
FROM TempTable
ORDER BY FullPath
-
Ummm... correct me if I'm wrong, please... the code still presupposes that you manually maintain the sort order...
--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) -
I had a similar problem like you,
Heres my solution to it. Would welcome expert opinions, if its acceptable.
My table consists of 4 columns
ParentId,NodeId(this is the child node), LevelId, TreeStructure(i just added this to simulate your question)
my business scenario:
A procedure returns all the child nodes( and its sub nodes) for a parent node in a hierarchy.
I had to store that in a temporary table and then starting from the queried node(now becomes root node) i have to drill down to assign the child nodes a levelId.
***************
Assumptions:
The mapppingTest table has only the root node and its child nodes.Otherwise this procedure wont work(goes into infinite loop)
1) create the table mappingTest
create table mappingTest(
ParentId nvarchar(100),
NodeId nvarchr(100),
LevelId int,
TreeStructure nvarchar(100)
)
insert into mappingTest(ParentId,NodeId) values ('AB12','ABC13')
insert into mappingTest(ParentId,NodeId) values ('AB12','ABC14')
insert into mappingTest(ParentId,NodeId) values ('AB01','AB12')
insert into mappingTest(ParentId,NodeId) values ('AB01','AB13')
insert into mappingTest(ParentId,NodeId) values ('ABC13','ABC131')
insert into mappingTest(ParentId,NodeId) values ('ABC13','ABC132')
insert into mappingTest(ParentId,NodeId) values ('ABC13','ABC133')
insert into mappingTest(ParentId,NodeId) values ('ABC14','ABC141')
insert into mappingTest(ParentId,NodeId) values ('ABC14','ABC142')
This procedure set the root nodes LevelId as 0, and drills down to assign levels under it
CREATE proc [dbo].[AssignNodes]
@RootNode nvarchar(255) = ''
as
declare @level int,
@TreeNode nvarchar(100)
-- Update the table with root level (ie set level = 1), this is our starting node in hierarchy
set @level=1
set @TreeNode='-'
update MappingTest
set LevelId=@level,
TreeStructure = TreeNode+NodeId
where ParentId=@RootNode
-- step 1 : We do a recursive loop here, by extracting all the rows where levelId is null
-- : The loop exits when all the levelId's are set
while(select count(*) from MappingTest where LevelId is null)>0
BEGIN
--Get the distinct of child nodes for which levelId is already set with latest @level parameter
--This way we only get the immediate parent nodes for child nodes. Now increment
--the level by updating the LevelId as LevelId = @level+1
-- set @TreeNode=@TreeNode+'-'
Update MappingTest set LevelId=@level+1,TreeStructure=@TreeNode+NodeId
where ParentId in(
Select distinct(NodeId)
from MappingTest
where levelId = @level)
--Increment the variable @level
set @level=@level+1
set @TreeNode = @TreeNode+'-'
END
**** One word of caution:
If the table has higher hierarchy level present in it, the proc will go into infinite loop. Table must only be filled with a particular hierarchy level and below only.
-
Jeff Moden (7/10/2008)
Ummm... correct me if I'm wrong, please... the code still presupposes that you manually maintain the sort order...Yes, I am maintaining a sort order manually in this one. Mark's solution to the sort order using the label name seems like a perfect solution for that, but I wanted to challenge myself to do something a little different. Instead of forcing a sort order by a label, I am using a column where an application could easily control the sort; allowing users to run a stored procedure and change the order within a parent node. I was thinking of menu-type hierarchies, where the order may be based on a logical method independent of the name of the label. As long as each "sort order" within a parent is unique (including the root "NULL" parent ID) and the columns used in the sort order are reproducible at the final select sort and in the "ROW_NUMBER" partition by sort the base of this SQL should yield a properly structured tree.
-
There is a method for this in Books Online called "expanding hierarchies"... it has about the same amout of RBAR as a recursive subquery (recursive subqueries are not set based despite their appearance). I also have another method hidden in my archives... I'll see if I can find it... haven't used it in years. Both methods use and return the data in the hierarchical order stipulated by the parent/child relationship that preserves the "natural" order of the tree.
--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)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply