Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hierarchy Structure SQL Query Expand / Collapse
Author
Message
Posted Wednesday, August 18, 2010 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 10, 2012 9:39 AM
Points: 1, 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

Post #971441
Posted Wednesday, August 18, 2010 2:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 1,945, Visits: 2,895
You are using an Adjacency List model. You might consider a Nested Sets Model instead, since it will do aggregation much easier and faster. You can Google it or get a copy of my TREES & HIERARCHIES IN SQL for details.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #971484
Posted Sunday, October 24, 2010 11:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
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 ) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1009793
Posted Thursday, May 24, 2012 5:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 25, 2012 1:18 AM
Points: 1, Visits: 4
AWESOME thanks so much for this post.
It helped us out greatly!
Post #1306247
Posted Thursday, May 24, 2012 6:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!
Post #1306260
Posted Thursday, May 24, 2012 9:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1306294
Posted Thursday, May 24, 2012 9:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1306295
Posted Thursday, May 24, 2012 9:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!
Post #1306299
Posted Thursday, May 24, 2012 10:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1306312
Posted Thursday, May 24, 2012 11:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!
Post #1306314
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse