SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchy Structure SQL Query


Hierarchy Structure SQL Query

Author
Message
KSeet
KSeet
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 8
I have a tree structure is like this

- Apartment
-Building 1508
- Room 210
- Room 211
- Building 1509
- Building 1510

I have output of a SQL query as

ID |AssetID| ParentID|Asset Name | Node
------------------------------------------------------------------
1 3319 3214 Apartment 1
2 3320 3319 Building 1508 2
3 3321 3319 Building 1509 2
4 3322 3319 Building 1510 2
5 3323 3320 Room 210 3
6 3324 3320 Room 211 3

What I need in out put as below . Please suggest a SQL for this

ID AssetID ParentID Asset Name Node
------------------------------------------------------------------
1 3319 3214 Apartment 1
2 3320 3319 Building 1508 2
3 3323 3320 Room 210 3
4 3324 3320 Room 211 3
5 3321 3319 Building 1509 2
6 3322 3319 Building 1510 2

HERE is the script generate the data
----------------------------------------------
CREATE TABLE #tblAsset(ID int IDENTITY(1,1),AssetID bigint,ParentID bigint,Asset varchar(90),Node int)
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node)VALUES(3319,3214,'Apartment',1)
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3320,3319,'Building 1508',2)
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3321,3319,'Building 1509',2)
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3322,3319,'Building 1510',2)
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES(3323,3320,'Room 210',3)
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3324,3320,'Room 211',3)
SELECT * FROM #tblAsset
DROP TABLE #tblAsset

Will appriciate your help
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215298 Visits: 41979
Oh my. KSeet... allow me to apologize for 1.2 million members. You did absolutely everything right in posting table structure, data, desired results, etc, etc, and, still, no one helped unless you believed in the spam and bought a book.

Joe Celko is correct... your data is an "Adjaceny List". He's also correct about "Nested Set" hierarchies being a bit easier to calculate with. There's another type of hierarchy called by many names (sometimes bad names in frustration if someone doesn't know how to work them :-P) but I usually refer to them as "Hierarchical Path" or "Sort Path". Each type has advantages and disadvantages depending on what you need to do. Note that "Hierarchical Path" is nearly identical to the HierarchyID in SQL Server 2008. I just don't have it on the box I'm working from today but building it takes code nearly identical to what I have below.

For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.

Here's both your test data setup and the code to produce the output you asked for... I also modified the code to indent the items by level. You can, of course, easily remove that.

-- DROP TABLE #tblAsset;
CREATE TABLE #tblAsset(ID int IDENTITY(1,1),AssetID bigint,ParentID bigint,Asset varchar(90),Node int);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node)VALUES(3319,3214,'Apartment',1);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3320,3319,'Building 1508',2);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3321,3319,'Building 1509',2);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3322,3319,'Building 1510',2);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES(3323,3320,'Room 210',3);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3324,3320,'Room 211',3);

WITH
cteBuildPath AS
(--==== This is the "anchor" part of the recursive CTE
SELECT anchor.AssetID,
anchor.ParentID,
anchor.Asset,
anchor.Node,
1 AS HLevel,
CAST(CAST(anchor.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM #tblAsset AS anchor
WHERE Node = 1
UNION ALL -------------------------------------------------------------------
--==== This is the "recursive" part of the CTE that adds 1 for each level
-- and concatenates each level of AssetID's to the SortPath column.
SELECT recur.AssetID,
recur.ParentID,
recur.Asset,
recur.Node,
cte.HLevel + 1 AS HLevel,
CAST(cte.SortPath + CAST(Recur.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM #tblAsset AS recur
INNER JOIN cteBuildPath AS cte
ON cte.AssetID = recur.ParentID
)--==== This final INSERT/SELECT creates the "narrow surrogate" column (NodeNumber)
SELECT AssetID, ParentID, Asset = SPACE((Hlevel-1)*4)+Asset, Node --,SortPath
FROM cteBuildPath
ORDER BY SortPath
;




Here's the result...
AssetID              ParentID             Asset                          Node
-------------------- -------------------- ------------------------------ -----------
3319 3214 Apartment 1
3320 3319 Building 1508 2
3323 3320 Room 210 3
3324 3320 Room 211 3
3321 3319 Building 1509 2
3322 3319 Building 1510 2



One final thing... notice the hierarchy level I calculated... it turns out to be the same as "Node". I don't know if "Node" was intended to be the "level", but it sure does look that way.

Again... I'm not sure what happened and why no one reached out to help especially since you followed ALL the "rules" of posting on your very first post.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
abr 70501
abr 70501
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 4
AWESOME thanks so much for this post.
It helped us out greatly!
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17833 Visits: 6431
Jeff Moden (10/24/2010)
For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.


Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215298 Visits: 41979
abr 70501 (5/24/2012)
AWESOME thanks so much for this post.
It helped us out greatly!


Very cool. Thanks for the feedback. Do you have any additional questions on this subject?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215298 Visits: 41979
dwain.c (5/24/2012)
Jeff Moden (10/24/2010)
For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.


Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.


Only by a year and eight months. :-) (Just having a little fun here).

Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17833 Visits: 6431
Jeff Moden (5/24/2012)
dwain.c (5/24/2012)
Jeff Moden (10/24/2010)
For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.


Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.


Only by a year and eight months. :-) (Just having a little fun here).

Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.


I have yet to beat one of your solutions in a timing competition, so what makes you think I could do so here? Not for lack of trying mind you.

Seriously, I will continue to try. I am still looking for that "killer app" for recursive CTEs that no one has thought of before.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215298 Visits: 41979
dwain.c (5/24/2012)
...so what makes you think I could do so here?


Not sure. You DO have a love for rCTEs and I'm thinking that you'll pull a rabbit out of your hat just because you do love working with them.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17833 Visits: 6431
Jeff Moden (5/24/2012)
dwain.c (5/24/2012)
...so what makes you think I could do so here?


Not sure. You DO have a love for rCTEs and I'm thinking that you'll pull a rabbit out of your hat just because you do love working with them.


Actually I think you're confusing my exploration with love.

I have kind of a fondness/hate relationship with them.

I'm fond of them because they're cool and interesting. I hate them because mostly they don't perform so well and because I am recursively challenged. And also because I haven't found that killer app yet (but will keep on looking).


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search