Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Displaying Sorted Hierarchies (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, March 14, 2011 4:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 6,136, Visits: 7,185
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
Post #1078052
Posted Monday, March 14, 2011 8:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1078091
Posted Monday, March 14, 2011 9:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 6,136, Visits: 7,185
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
Post #1078100
Posted Monday, March 14, 2011 9:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1078105
Posted Tuesday, March 15, 2011 3:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1078693
Posted Wednesday, November 30, 2011 3:53 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 28, 2013 9:38 PM
Points: 3, Visits: 34
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

Post #1214285
Posted Saturday, August 17, 2013 12:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485512
Posted Saturday, August 24, 2013 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 9:39 AM
Points: 7, Visits: 21
Great post!
So, how can I do this with desc order by?
Post #1488121
Posted Monday, January 27, 2014 9:30 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 7:31 AM
Points: 117, Visits: 176
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.



Post #1535090
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse