|
|
Feb
25
Written by:
Allen Berezovsky
2/25/2009 6:14 PM
A former colleague of mine called me the other day with a following problem. She needed to write some reports against a self referencing table. Her initial thought was that she needed to create a number of temp tables, and fill them with the data for each hierarchy level. Her problem also was that she had no idea how deep the hierarchy went. When she got tired of running all kinds of select statements to find out the depth of that hierarchy, she called me for advice. Here is the sample code that I sent her.
Feel free to run this against the AdventureWorks database, and check out the results. The HumanResources.Employee table has an EmployeeID column, and a ManagerID column that references the EmployeeID within the hierarchy. The only catch is, this will not work in SQL 2000 or below.
1: WITH CorpStructure(ManagerID,EmployeeID, Title, Level)
2: AS
3: (
4: SELECT Emp.ManagerID, Emp.EmployeeID, Emp.Title, 1 as Level
5: FROM HumanResources.Employee as Emp
6: WHERE ManagerID is NULL
7: UNION ALL
8: SELECT Emp.ManagerID, Emp.EmployeeID, Emp.Title, Corp.Level + 1
9: FROM HumanResources.Employee as Emp INNER JOIN CorpStructure as Corp
10: ON Emp.ManagerID = Corp.EmployeeID
11: )
12: SELECT ManagerID, EmployeeID, Title, Level
13: FROM CorpStructure
Tags:
142 comments so far...
Re: Hierarchy of a self referencing table
nice!
By John on
4/16/2009 9:38 PM
|
Re: Hierarchy of a self referencing table
Jouer casino remarquablement simple et convivial explication du complexe pour beaucoup de choses!
By aster on
1/15/2010 8:13 AM
|
Re: Hierarchy of a self referencing table
I have examined almost all the posts on your blogging resource and I would like to go on but I terribly want to sleep. It’s 4 a.m. Next day – new life. flash programming
By Leon on
2/5/2010 3:04 AM
|
Re: Hierarchy of a self referencing table
Astonishing! I am very glad that I dropped here today. I like your blog very much and I hope to read more of your writing as I think it is worth it!
flex chat
By James on
3/17/2010 2:41 AM
|
|
|
Re: Hierarchy of a self referencing table
Looking For discountchanel replica
Looking For discountchanel handbags
Looking For discountchanel
Beautifulgucci handbags for sale
Beautifulgucci for sale
The store online sells theBalenciaga bags
Balenciagafor sale
If you want to buylouis vuitton bag
If you want to buylouis vuitton
different kinds oflouis vuitton handbag for cheap
different kinds oflouis vuitton for cheap
louis vuitton bag
replica louis vuitton bag for Cheap Wholesalecheap
replica louis vuitton bag for for Cheap Wholesale
china Wholesale
replica louis vuitton
cheapreplica louis vuitton handbagsfor sale
chanel handbags
gucci handbags
louis vuitton shoesfor wholesale
cheap and nicegucci shoesfor sale
different kinds ofshoes
chanel bagsfor sale
chanel handbagsfor discount
large number discountchanel
By lv on
3/18/2010 10:29 PM
|
777777777777
[url=http://www.salelouboutin.com]christian louboutin[/url]
[url=http://www.buylouboutin.com]louboutin[/url]
[url=http://www.bestlouisvuitton.com]lv handbags[/url]
[url=http://www.sale-mbt.com]mbt shoes[/url]
[url=http://www.discount-christianlouboutin.com]christian louboutin[/url]
[url=http://www.sale-mbt.com/nike-shox-c-29.html]nike shoes[/url]
[url=http://www.sale-mbt.com/nike-shox-c-29.html]cheap nike shoes[/url]
[url=http://www.sale-mbt.com/nike-shox-c-29.html]discount nike shoes[/url]
[url=http://www.sale-mbt.com/nike-shox-c-29.html]nike shoes sale[/url]
[url=http://www.sale-mbt.com/moncler-women-c-19.html]moncler women[/url]
[url=http://www.sale-mbt.com/moncler-women-c-19.html]cheap moncler women[/url]
[url=http://www.sale-mbt.com/moncler-women-c-19.html]discount moncler women[/url]
[url=http://www.sale-mbt.com/moncler-women-c-19.html]moncler women sale[/url]
[url=http://www.sale-mbt.com/moncler-men-c-17.html]moncler men[/url]
[url=http://www.sale-mbt.com/moncler-men-c-17.html]cheap moncler men[/url]
[url=http://www.sale-mbt.com/moncler-men-c-17.html]discount moncler men[/url]
[url=http://www.sale-mbt.com/moncler-men-c-17.html]moncler men sale[/url]
[url=http://www.sale-mbt.com/moncler-kids-c-18.html]moncler kids[/url]
[url=http://www.sale-mbt.com/moncler-kids-c-18.html]cheap moncler kids[/url]
[url=http://www.sale-mbt.com/moncler-kids-c-18.html]discount moncler kids[/url]
[url=http://www.sale-mbt.com/moncler-kids-c-18.html]moncler kids sale[/url]
[url=http://www.sale-mbt.com/]cheap mbt shoes[/url]
[url=http://www.sale-mbt.com/]discount mbt shoes[/url]
By 33333333333 on
3/25/2010 5:57 PM
|
Re: Hierarchy of a self referencing table
By 33333333333 on
3/25/2010 6:09 PM
|
gucci shoes
lisa
Nike Air Force 1 Ones
Nike Air Jordan Shoes
Nike Shox
By gucci shoes on
4/7/2010 6:43 PM
|
hary This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball su
hary
This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball superstar Michael Jordan shoes:Air Jordan 3 III, Air Jordan 4 IV, By air-jordan on
4/12/2010 4:12 PM
|
hary This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball su
hary
This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball superstar Michael Jordan shoes:Air Jordan 3 III, Air Jordan 4 IV, By air-jordan on
4/12/2010 4:12 PM
|
hary This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball su
hary
This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball superstar Michael Jordan shoes:Air Jordan 3 III, Air Jordan 4 IV, By air-jordan on
4/12/2010 4:12 PM
|
hary This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball su
hary
This article is very good, describes some of the things I never know, there are many new things put in front of me, for example, a lot of valuable goods inside the Online Shop but others have no, there are several categories of shoes, a basketball superstar Michael Jordan shoes:Air Jordan 3 III, Air Jordan 4 IV, By air-jordan on
4/12/2010 4:13 PM
|
110
April 2010 A grand finale wow cd keyended completely banal, aion cd keywith a scene, as wow time cardwe see them in every eve time cardhandball game a dozen times - aion time carda passing did not reach its xbox 360 live pointswow goldaddressee. The partisan crowd had wow goldprobably already begun to world of warcraft goldrejoice after 70 nail-biting wow power levelingwow time cardminutes on a Siebenmeterwerfen wow cdkeywow time cardas lions professional Olafur xbox live 12 month cardxbox 360 live pointsStefansson the ball over a few eve game cardguildwars cdkeyguild wars cd keymeters not zupasste call of duty 6 codepsn cardMr Uwe Gensheimer Playstation Network Cardmass effect 2 cdkeyfor possibly the last equalized mass effect 2 codessro goldthe extended playing time, silkroad online goldaion kinahaion powerlevelingbut a hamburger in the hands.wow goldwow goldThe Rhein-Neckar L?wen wow goldwow goldwow goldwaited consternation to a last chaos goldlastchaos goldreferee whistle in their favor, wow goldwow goldbut did not, it was very wow powerlevelingwow time carddifficult to say whether a foul had influenced playback or acceptance.
By bmx on
4/18/2010 9:59 PM
|
|
|
|
| |