July 19, 2012 at 12:41 am
Hi Friends,
I am facing a situation where I need to find out what exactly number of children a parent have in Hierarchy ID.
any help in this regard is very much appreciated.
Thanks.
July 19, 2012 at 1:45 am
Please provide DDL of the tables involved, some sample data and expected results
If you don't know how to do this, check the link in my signature
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 19, 2012 at 2:04 am
OKay, I have columns name "Description" Charactor type and "SiteMapNode" of HierarchyID type. what I do is that i want to insert a new node (Description) and and its respective parent name (Description) , i Check if this if this (Parent) Description matches any of the descriptions in the data. if it exsists then i need to know total number of children of the parent..
And this is the problem, I can't find out total children of the parent of the existing node that is about to be inserted.
e.g :
New Node (Description) : Emplyee
Parent Node (Description) : Manager
Total Children (Employee) of Manager = ??? need to be found !
July 19, 2012 at 2:11 am
Please provide us the create table and its sample data along with the sample reultset. Someone would definitely able to help you better in that case.
July 19, 2012 at 5:46 am
here is the Structure of the Table
Description Node(HierarchyID) StringPath
Manager 0x /
Dev Manager 0x58 /1/
QA Manager 0x68 /2/
Employee1 0x6AC0 /2/1/
Employee2 0x6B40 /2/2/
I want to insert Employee3 under QA manager, so i need to verify whether QA manager is its parent, if it is then how manay ohter children his parent have. i need the count. Remeber, i m doing all these through an SP.
I hope this is more explainatory 🙂
Regards,
Celestial
July 19, 2012 at 8:23 am
Look at the heirarchy data type - If you use that can, I seem to remember you can get that info very quickly
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
July 19, 2012 at 10:25 am
Celestial (7/19/2012)
here is the Structure of the TableDescription Node(HierarchyID) StringPath
Manager 0x /
Dev Manager 0x58 /1/
QA Manager 0x68 /2/
Employee1 0x6AC0 /2/1/
Employee2 0x6B40 /2/2/
I want to insert Employee3 under QA manager, so i need to verify whether QA manager is its parent, if it is then how manay ohter children his parent have. i need the count. Remeber, i m doing all these through an SP.
I hope this is more explainatory 🙂
Regards,
Celestial
No that is not what anybody was asking for. If you had read the article you would have figured out we want ddl, sample data and desired output in a consumable format.
Here is an example of what your ddl might look like along with some sample data.
create table #SomeTable
(
Manager varchar(25),
Node hierarchyid,
StringPath varchar(10)
)
insert #SomeTable
select 'Manager', 0x, '/' union all
select 'Dev Manager', 0x58, '/1/' union all
select 'QA Manager', 0x68, '/2/' union all
select 'Employee1', 0x6AC0, '/2/1/' union all
select 'Employee2', 0x6B40, '/2/2/'
OK so now you want to find the number of children for any given node. Notice that with the ddl and data already defined this becomes a lot less work for the volunteers around here to work on your problem instead of working on setting up your problem.
Something like this should work to get the desired results as I understand them.
declare @NodeToFind hierarchyid
select @NodeToFind = Node from #SomeTable where Manager = 'QA Manager'
select COUNT(*) - 1 as NumChildren --we use -1 because the IsDescendantOf will always return true for itself
from #SomeTable
where Node.IsDescendantOf(@NodeToFind) = 1
drop table #SomeTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2012 at 1:14 am
Thank you very much Sean,
This is what i was asking for but, i couldn't explain my point. the only problem is that I want the Immediate Parent of the node to be inserted.
if the @NodeToFind is not itself, but rather one from its ancestors then i m not going to insert Node under this one.
declare @NodeToFind hierarchyid
declare @count int
select @NodeToFind = Node from #SomeTable where Manager = 'Manager'
select @count=COUNT(*) - 1 --as NumChildren --we use -1 because the IsDescendantOf will always return true for itself
from #SomeTable
where Node.IsDescendantOf(@NodeToFind) = 1
print @count
insert into #SomeTable (Manager,Node,StringPath)
select 'Employee3', '/',CAST(@NodeToFind as varchar)+ cast((@count+1) as varchar) +'/'
update #SomeTable set Node = StringPath
where manager = 'Employee3'
in the above code i am trying to insert a node, as "Manager" is not the Immediate parent of the "Employee3",so i don't want to insert this node under "Manager". but the problem is that how would i know whether "Manager" is its Immediate parent or not ?? how should i put this constraint on it ??
Please Help me on this.
July 20, 2012 at 7:19 am
I don't quite get what you are doing but doing inserts into a hierarchy like this is pretty straight forward.
http://msdn.microsoft.com/en-us/library/bb677174.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2012 at 9:45 am
With HierarchyID datatype, you can use the GetAncestor() method to find all rows that have a given ancestor at a given level.
If, for example, you want to find all rows that have a particular boss as their immediate answer-to (it's their boss, not their boss's boss, and so on), then you use GetAncestor(1), which goes up one level. The documentation on it even has an example for finding everyone who has a specific boss. If you want to get the quantity, just build it as a Count(*) query with GetAncestor(1) in the Where clause.
You can find documentation on all the HierarchyID datatype methods here: http://technet.microsoft.com/en-us/library/bb677193.aspx
They're very useful, and usually quite fast, even on large tables with complex hierarchies.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2012 at 10:46 pm
Thank you all, especially Sean & GSquared, your answers really helped me solving my problem.
Thanks Again 🙂
July 25, 2012 at 6:58 am
You are welcome. Glad you were able to figure out a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 7:15 am
Celestial (7/24/2012)
Thank you all, especially Sean & GSquared, your answers really helped me solving my problem.Thanks Again 🙂
Just to ask the questions to make sure you're really all set...
1. How many nodes do you have in your hierarchy?
2. How often does the hierarchy change?
3. Do you need a count of the sub-tree of nodes reporting to a node for every node in the tree?
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply