quote:
Originally naively posted by skip_203this looks trivial with a simple outer join, but since the number of levels is large (10) I end up with a horrendous number of self-joins.
No kidding. After some heavy Googling, it seems like this question comes up all the time so I thought it would be helpful to post how I figured out a pretty cool way to do this:1. Firstly, all my attempts using a set-oriented approach with aliased outer joins failed. Outer joins did preserve the staff hierarchy order, but collapsed the hierarchy so that the number of rows in the result set were less than in the source table since managers were grouped by staff.2. A recursive approach is really what's needed to convert the table, but the hierarchy order aligned horizontally is difficult to maintain. After a couple attempts to iterate through the list using a cursor, I realized that I would need to re-order the source table for a recursive function to work. Attempting this with a cursor was daunting, so I revisited common table expressions.3. One feature I discovered with common table expressions was the ability for them to pass data between expressions. This feature is lightly covered in the CTE examples, but is amazingly powerful for recursive operations.4. Okay, step 1 -- reorganize source table into a vertical ordered hierarchy:HAVING sortEmployees (Depth, ManagerID, EmployeeID, OrgPath)AS ( SELECT 0 AS Depth, ManagerID, EmployeeID, CAST(EmployeeID AS VARCHAR(MAX)) AS OrgPath FROM Employee WHERE ManagerID = 'CEO' UNION ALL SELECT sortEmployee.Depth + 1 AS Depth, Employee.ManagerID, Employee.EmployeeID, CAST(sortEmployees.EmployeeID + '\' + Employee.EmployeeID AS VARCHAR(MAX)) AS OrgPath FROM Employee INNER JOIN sortEmployees ON Employee.ManagerID = sortEmployees.EmployeeID) -- Outer select SELECT Depth, ManagerID, EmployeeID, OrgPath FROM sortEmployee ORDER BY OrgPath 5. Step 2: once the org hierarchy is sorted vertically, a recursive CTE can easily convert the table using CASE. This is where the ability to pass values between the anchor to the recursive queries in the CTE saved the day.DECLARE @empty varchar(30) --need to match type between anchor and recursive query; HAVING pivotEmployee (Depth, EmployeeID, LEVEL1, LEVEL2, LEVEL3)AS (SELECT Depth, EmployeeID, EmployeeID AS LEVEL1, @empty AS LEVEL2, @empty AS LEVEL3FROM SortTableWHERE ManagerID = 'CEO'UNION ALLSELECT SortTable.Depth, SortTable.EmployeeID,CASE SortTable.Depth WHEN 0 THEN SortTable.EmployeeID ELSE pivotEmployee.LEVEL1 END AS LEVEL1,CASE SortTable.Depth WHEN 1 THEN SortTable.EmployeeID ELSE pivotEmployee.LEVEL2 END AS LEVEL2,CASE SortTable.Depth WHEN 2 THEN SortTable.EmployeeID ELSE pivotEmployee.LEVEL3 END AS LEVEL3FROM SortTable INNER JOIN pivotEmployee ON SortTable.ManagerID = pivotEmployee.EmployeeID)SELECT LEVEL1, LEVEL2, LEVEL3FROM pivotEmployeeORDER BY LEVEL1, LEVEL2, LEVEL3 This solution is a little convoluted, but much, much easier than using cursor or UDF for a n00b like me and I'm definitely hooked on SQL 2005 CTE. Hope this approach helps other folks struggling with denormalizing hierarchical tables.Cheers,Skip |
Help with crosstab denormalize query?
Posted - 2005-12-08 : 20:20:28
