If it has just the two levels you indicated, it probably can be done via a self-join, but I suspect what you are asking is how to sort a hierarchy. If that is so, you would need what they refer to as materialized path. Here is a way of doing it - to show what I am trying to do, I constructed a test table; in your real query, you wouldn't need the test table.You may also want to invnestigate the use of hierarchyid data type.CREATE TABLE #tmp(id INT, extraid INT);INSERT INTO #tmp VALUES (1,NULL),(2,NULL),(3,2),(4,2),(5,1),(6,5),(7,4),(8,3),(9,3),(10,4);;WITH cte AS( SELECT *, CAST(ROW_NUMBER() OVER (ORDER BY id) AS VARCHAR(max)) AS MPath, 1 AS lvl FROM #tmp WHERE extraid IS NULL UNION ALL SELECT t.id, t.extraid, CAST(c.MPath+'/'+CAST(ROW_NUMBER() OVER (ORDER BY t.id) AS VARCHAR(255)) AS VARCHAR(MAX)), c.lvl+1 FROM cte c INNER JOIN #tmp t ON t.extraid = c.ID)SELECT * FROM cte ORDER BY MPath;DROP TABLE #tmp;
How to sort a table
Posted - 2012-01-11 : 10:15:09
