Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help with crosstab denormalize query?

Author  Topic 

skip_203
Starting Member

4 Posts

Posted - 2005-12-08 : 20:20:28
This has to come up often so I'm hoping it easy, but it's harder than I can figure out so any pointers would be a huge help.

I'm attempting to create a denormalized result set from our employee table that can be imported into an Excel Pivot table and I'm struggling with how to approach displaying the multiple levels of the org hierarchy. Basically, I need a query that returns the staff in separate columns by org level.

Source table: Employee

Level PID EmpEmail ManagerID ManagerEmail
----------------------------------------------------
1 1000 CEO NULL NULL
2 1001 VP1 1000 CEO
2 1002 VP2 1000 CEO
3 1003 FOO1 1001 VP1
3 1004 F002 1002 VP2
4 1005 BAR1 1003 FOO1
4 1006 BAR2 1003 FOO1


I need to denormalize the table for the Excel pivot table: TEMP_Table

Org1 Org2 Org3 Org3
--------------------------------------------------
CEO VP1 FOO1 NULL
CEO VP1 FOO1 BAR1
CEO VP1 FOO1 BAR2
CEO VP2 FOO2 NULL


Again, this 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. Any hints would be a huge help.

Thanks!

skip_203
Starting Member

4 Posts

Posted - 2005-12-11 : 01:42:01
quote:
Originally naively posted by skip_203
this 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 LEVEL3
FROM SortTable
WHERE ManagerID = 'CEO'
UNION ALL
SELECT 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 LEVEL3
FROM SortTable INNER JOIN
pivotEmployee ON SortTable.ManagerID = pivotEmployee.EmployeeID)

SELECT LEVEL1, LEVEL2, LEVEL3
FROM pivotEmployee
ORDER 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





Go to Top of Page
   

- Advertisement -