Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IK1972
56 Posts |
 Posted - 2012-09-07 : 12:14:35
|
| I have one table with 4 columnsID, SourceID, TargetID, TargerType 1, 123, 456, 42, 456, 789, 33, 456, 789, 4 4, 789, 123, 2Id is unique key. SourceID is UserIDTargetID is User Manager IDTargetType is Manager RoleI need to get user hierarchy from bottom to top. User to Manager and then Manager to Sr. Manager and then Sr. Manager to VP and from VP to SVP and from SVP to Director. Thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-07 : 12:31:09
|
| The sample data that you posted does not seem to be valid for an organizational hierarchy as you described, because it has circular references.If you did not have circular references, you can use a recursive CTE to get the data you are looking for. This page on MSDN in fact has an organizational hierarchy example: http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspxWhen you do have circular references, recursive CTE's will miserably fail because they will go into an infinite loop, or wouldn't know where to start or how to end the query - unless you can program the rules for all that into your query. |
 |
|
|
IK1972
56 Posts |
Posted - 2012-09-07 : 12:51:50
|
| Like you can amuse I don't have circular ref. Is there any third party free tool available to use user hierarchy data or is it possible we can create XML based on our data and then use some tool to display hierarchy data in GUI format.I actual goal is to see User hierarchy in GUI live we can create in Visio.ThanksInayat |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-07 : 13:03:08
|
quote:
Originally posted by inayat1972
Like you can amuse I don't have circular ref.
My apologies. I may have misunderstood the data which led me to believe that there are circular references. |
|
|
|
IK1972
56 Posts |
Posted - 2012-09-07 : 15:16:50
|
| no problem sunitabeck, do you know how to get the complete hierarchy |
|
|
|
|
|
|
|
|
User hierarchy