Recursive? SOS Need HELP

Question

Post reply

Recursive? SOS Need HELP

Barry J. Stratta

Mr or Mrs. 500

Points: 557
More actions
December 9, 2010 at 9:16 am

#228321

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...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Barry J. Stratta Mr or Mrs. 500 Points: 557 More actions · Answer 2

Guess I'm in the wrong area... using Microsoft SQL Server 2008

Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )

Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)

Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer 7.0.5730.13

Microsoft .NET Framework 2.0.50727.3615

Operating System 5.1.2600

LutzM SSC Guru Points: 107049 More actions · Answer 3

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Barry J. Stratta Mr or Mrs. 500 Points: 557 More actions · Answer 4

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...

^{employeeID ManagerID}

111111111 222222221

111111112 222222222

111111113 222222223

111111114 222222224

222222221 222222225

222222225 222222226

111111117 222222227

111111118 222222228

222222226 222222229

222222229 222222230

employeeID ManagerID ManagerID_2 ManagerID_3 ManagerID_4 ManagerID_5 ManagerID_6 ManagerID_7 ManagerID_8 ManagerID_9 ManagerID_10

111111111 222222221 222222225 222222226 222222229 222222230

111111112 222222222

111111113 222222223

111111114 222222224

222222221 222222225 222222226 222222229 222222230

222222225 222222226 222222229 222222230

111111117 222222227

111111118 222222228

222222226 222222229 222222230

222222229 222222230

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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.

- Craig Farrell

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