December 9, 2010 at 9:16 am
Desperate for some TSQL to get one or if possible two results...
My data (table)
Column1 |Column2
employee |manager
My table has records where not all managers are listed as employees and this is where the "chain" would stop..
I'm trying to find a way to get the e1>m1>m2?>m3?>m4?>M5????>>>>
SO I'm thinking some type of cursors DO WHILE procedure but do not know how to do it so I can can get a concatenation of the mangers for each employee... and the bonus would be to get the count/value of the employee with the longest chain.. .where manager is employee where manger is employee....:crying:
Thanks for your help...
December 9, 2010 at 3:13 pm
Please post table def and sample data in a ready to use format as described in the first link in my signature. Also, please include the expected result based on the sample data provided together with what you've tried so far.
And please confirm that you're using SQL 200 or below.
December 9, 2010 at 3:44 pm
Guess I'm in the wrong area... using Microsoft SQL Server 2008
Management Studio10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.5730.13
Microsoft .NET Framework2.0.50727.3615
Operating System5.1.2600
December 9, 2010 at 3:57 pm
Ok, after clarification of the system you're using we'd still need some ready to use sample data....
I don't think you'd need to ask the question again in the SS2K8 forum. There'll be enough people around recognizing your post. I hope.
December 9, 2010 at 3:59 pm
What I have is a table with two fields with employeeid and managerid... I want to find if an employeeid has a manger id if is also an employeeid in the table them get next managerid and so on for all employees in the table and end up with a new table with e,m,m2,m3,m4, m5, m6, m7... or as as many as there may be... of course some whill be null after e, m because the m will not be in the e list... I guess what I need is a way to build a tree span of the data ultimately I would like to know what employee has the longest manger chain... and after the first tow columns of e, m the 3rd column could have all ids bundled together and I could count the characters to find out how many are in it if its easier than making a new column for every up-level manager...
employeeIDManagerID
111111111222222221
111111112222222222
111111113222222223
111111114222222224
222222221222222225
222222225222222226
111111117222222227
111111118222222228
222222226222222229
222222229222222230
employeeIDManagerIDManagerID_2ManagerID_3ManagerID_4ManagerID_5ManagerID_6ManagerID_7ManagerID_8ManagerID_9ManagerID_10
111111111222222221222222225222222226222222229222222230
111111112222222222
111111113222222223
111111114222222224
222222221222222225222222226222222229222222230
222222225222222226222222229222222230
111111117222222227
111111118222222228
222222226222222229222222230
222222229222222230
December 9, 2010 at 4:02 pm
Sounds like standard org chart recursion.
Have a gander on the intertubes for 'Recursive CTE'. That'll handle most of your question.
If you'd like some tested code, please check out the first link in my signature. It'll help you pull your table DDL out and show you how to setup your data so everyone can consume it easily so they have a testbed that can solve exactly what you need.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply