SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Displaying Sorted Hierarchies (SQL Spackle)


Displaying Sorted Hierarchies (SQL Spackle)

Author
Message
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8583 Visits: 7660
A bit ignorant here, what's an MLM?


- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85651 Visits: 41082
Craig Farrell (3/14/2011)
A bit ignorant here, what's an MLM?


Multi-Level Marketing. Think, Amway, ACN, Avon, and a whole host of other companies similar in nature.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8583 Visits: 7660
Jeff Moden (3/14/2011)
Craig Farrell (3/14/2011)
A bit ignorant here, what's an MLM?


Multi-Level Marketing. Think, Amway, ACN, Avon, and a whole host of other companies similar in nature.


AH! Legal Pyramid Schemes! Gotcha!


- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85651 Visits: 41082
Craig Farrell (3/14/2011)

AH! Legal Pyramid Schemes! Gotcha!


Heh... I have mixed emotions about them. The ones that run a Uni-Level payout matrix and require you to sell product and not just stack up representatives are some of the best. People CAN make money without being at the "top". The people who fail to run their business correctly (usually boils down to being lazy) seem to get the most press and, of course, they have nothing good to say about the companies.

Don't get me wrong... there are some really bad, bad, bad ones out there, as well but the industry, in general, has a legacy reputation that it no longer deserves.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85651 Visits: 41082
@Sean,

Did you get my email?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sykiemikey
sykiemikey
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 47
Excellent article Jeff! You just saved me quite a bit of sanity

I was needing to sort by an alphanumeric name field and finally found a suitable way that doesn't seem to break.

Here it is if anyone else has need of it.
The main difference is the HierarchicalPath.
You could essentially put ANY column in the OVER(ORDER BY) function and get a perfect sort.
Mine assumes I'll never have more than 99999 items.


WITH DirectPrograms (ProgramParent, ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath)
AS
(
SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = 0,
HierarchicalPath = CAST('\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100))
FROM ys2.PROGRAM AS p
WHERE ProgramParent IS NULL
UNION ALL
SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = [Level] + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100))
FROM ys2.PROGRAM AS p
INNER JOIN DirectPrograms AS d
ON p.ProgramParent = d.ProgramID
)
SELECT ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath
FROM DirectPrograms
ORDER BY HierarchicalPath


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85651 Visits: 41082
Just in case those that already responded on this thread haven't seen them, the articles that I was talking about writing came out a while ago. Here are the links.

For some very high performance methods for building/maintaining (54 seconds for a million node hierarchy) from an Adjacency List and using Nested Sets in conjunction with both an Adjacency List and a Hierarchical Path all in one table, please see the following article.
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

For information on how to build a pre-aggregated hierarchical table (again... only takes about a minute) that answers for most of the things you'd ever query a hierarchical table for (MLM'ers will love this one), please see the following article.
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
denisribeiro
denisribeiro
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 32
Great post!
So, how can I do this with desc order by?
sholliday
sholliday
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 182
Is there a way to get the complete hierarchy for a single Employee?



declare @EmployeeID int
select @EmployeeID = 12

;WITH
cteDirectReports AS
(
SELECT EmployeeID, ManagerID, EmployeeName, EmployeeLevel = 1,
HierarchicalPath = CAST('\'+CAST(EmployeeName AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, EmployeeLevel = d.EmployeeLevel + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.EmployeeName AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.Employee e
INNER JOIN cteDirectReports d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID,
ManagerID,
EmployeeName = SPACE((EmployeeLevel-1)*4) + EmployeeName,
EmployeeLevel,
HierarchicalPath
FROM cteDirectReports

where EmployeeID = @EmployeeID

ORDER BY HierarchicalPath
;



My quick and futile attempt above (which I understand why it does not work) only gets:

12 8 Megan 4 \Jim\Bob\Bill\Megan

The desired result would be:

1 NULL Jim 1 \Jim
3 1 Bob 2 \Jim\Bob
8 3 Bill 3 \Jim\Bob\Bill
13 8 Kim 4 \Jim\Bob\Bill\Kim
12 8 Megan 4 \Jim\Bob\Bill\Megan


Thanks for the post.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search