Hierarchy Structure SQL Query

Question

Post reply

Hierarchy Structure SQL Query

Viewing 4 posts - 16 through 18 (of 18 total)

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

tafinami SSC Enthusiast Points: 135 More actions · Answer 1

DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR

SELECT Id FROM Tree_Table WHERE Id NOT IN (SELECT DISTINCT ParentId FROM Tree_Table WHERE ParentId IS NOT NULL)

DECLARE @IdPk INT

OPEN c

FETCH NEXT FROM c INTO @IdPk

WHILE (@@FETCH_STATUS = 0)

BEGIN

DECLARE @NodeValue VARCHAR(MAX)

SET @NodeValue = ''

WHILE(EXISTS(SELECT * FROM Tree_Table WHERE Id=@IdPk))

BEGIN

SELECT @NodeValue=Name+'/'+@NodeValue,

@IdPk=ParentId

FROM Tree_Table

WHERE id=@IdPk

END

PRINT 'Parent to Leaf: ' + LEFT(@NodeValue,LEN(@NodeValue)-1)

FETCH NEXT FROM c INTO @IdPk

END

CLOSE c

DEALLOCATE c

To get hierarchy and tree view data

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

tafinami (6/30/2014)
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT Id FROM Tree_Table WHERE Id NOT IN (SELECT DISTINCT ParentId FROM Tree_Table WHERE ParentId IS NOT NULL)
DECLARE @IdPk INT
OPEN c
FETCH NEXT FROM c INTO @IdPk
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @NodeValue VARCHAR(MAX)
SET @NodeValue = ''
WHILE(EXISTS(SELECT * FROM Tree_Table WHERE Id=@IdPk))
BEGIN
SELECT @NodeValue=Name+'/'+@NodeValue,
@IdPk=ParentId
FROM Tree_Table
WHERE id=@IdPk
END
PRINT 'Parent to Leaf: ' + LEFT(@NodeValue,LEN(@NodeValue)-1)
FETCH NEXT FROM c INTO @IdPk
END
CLOSE c
DEALLOCATE c
To get hierarchy and tree view data

In this case, a cursor isn't the worst thing in the world. However, take a look at the following articles...

http://www.sqlservercentral.com/articles/Hierarchy/94040/

http://www.sqlservercentral.com/articles/T-SQL/94570/

--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 SSC Guru Points: 1003869 More actions · Answer 3

Jeff Moden (5/27/2012)
RBarryYoung (5/26/2012)
Jeff Moden (5/26/2012)
You mean just as a separate post or here or did you have something else in mind, Barry?
Right, a separate post, with a data set (million rows, of course) and a formal statement of the challenge ("fastest execution time to produce result set like this ...".
I actually have a fair start on the data set...
Got it. I'll see what i can do. Thanks, Barry.

Wow... talking about a forgotten promise... my apologies, Barry.

That, notwithstanding, please see the following article which also has a million row "clean" hierarchy generator.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

This next one is part of the same series (2 articles in the series) and provides an alternate to all of the currently known hierarchical structures.

http://www.sqlservercentral.com/articles/T-SQL/94570/

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Jeff Moden (6/30/2014)
tafinami (6/30/2014)
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT Id FROM Tree_Table WHERE Id NOT IN (SELECT DISTINCT ParentId FROM Tree_Table WHERE ParentId IS NOT NULL)
DECLARE @IdPk INT
OPEN c
FETCH NEXT FROM c INTO @IdPk
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @NodeValue VARCHAR(MAX)
SET @NodeValue = ''
WHILE(EXISTS(SELECT * FROM Tree_Table WHERE Id=@IdPk))
BEGIN
SELECT @NodeValue=Name+'/'+@NodeValue,
@IdPk=ParentId
FROM Tree_Table
WHERE id=@IdPk
END
PRINT 'Parent to Leaf: ' + LEFT(@NodeValue,LEN(@NodeValue)-1)
FETCH NEXT FROM c INTO @IdPk
END
CLOSE c
DEALLOCATE c
To get hierarchy and tree view data
In this case, a cursor isn't the worst thing in the world. However, take a look at the following articles...
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/

Or this one, if Jeff's wonderful solutions on steroids overwhelm you:

The Performance of Traversing a SQL Hierarchy [/url]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St