April 6, 2015 at 4:03 pm
Hi,
I got assignment for one course and kinda struggling with last portion of it, how to make it appear in the right order .
Like illustrated in example, Looks like I tried all SORT options, will appreciate your help.
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
/* need output like this, *** I with purpose mix name characters to break natural sorting order for EmpID and ManID.
A
_MAC
__1ABA
____ABB
_XB
__BAC
*/
-- SELECT * FROM emp
; WITH cte1 AS ( -- partial solution
SELECT CAST(a.EmpID AS VARCHAR(20)) EmpID, A.ManID , CAST(ISNULL(a.ManID,'') AS VARCHAR(20)) + '_' +CAST(ISNULL(a.ManID,'') AS VARCHAR(20)) [Tree],
a.EmpName, 0 Lvl
FROM emp A WHERE A.ManID IS nULL
UNION ALL
SELECT CAST(b.EmpID AS VARCHAR(20)) C1, B.ManID , CAST(cte1.Tree AS VARCHAR(20)) + '_' +CAST(ISNULL(b.ManID,'') AS VARCHAR(20)) [Tree],
b.EmpName, cte1.Lvl + 1 Lvl
FROM emp B
INNER JOIN cte1 ON cte1.EmpID = B.ManID
SELECT REPLICATE('_',lvl) + EmpID Emp, Tree ,REPLACE(tree,'_','') Tree1, lvl FROM Cte1
ORDER BY REPLACE(tree,'_','') --???
)
April 7, 2015 at 3:30 pm
I actually was able to complete this task in SSRS doing group and sort, but just itching to do this in plain SQL.
Best
mario
April 7, 2015 at 5:56 pm
This article is rather complicated but it is the only place that immediately comes to mind to show you how to do this. At least I'm pretty sure it will give you what you want.
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url] by Jeff Moden
Look into how he constructs "SortPath"
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply