Hierarchy based aggregation

Question

I have a hierarchy table in SQL Server 2005 which contains employees -> managers -> department -> location -> state.

Sample table for hierarchy table:

ID   Name           ParentID   Type
1    PA             NULL       0 (group)
2    Pittsburgh     1          1 (subgroup)
3    Accounts       2          1 
4    Alex           3          2 (employee)
5    Robin          3          2
6    HR             2          1
7    Robert         6          2

Second one is fact table which contains employee salary details ID and Salary.

Sample data for fact table:

ID    Salary
4     6000
5     5000
7     4000

Is there any good to way to display the hierarchy from hierarchy table with aggregated sum of salary based on employees. Expected result is like

Name              Salary
PA                15000   (Pittsburgh + others(if any)) 
  Pittusburgh     15000   (Accounts + HR)
    Accounts      11000   (Alex + Robin)
      Alex         6000   (direct values)
      Robin        5000
    HR             4000
      Robert       4000

In my production environment, hierarchy table may contain 23000+ rows and fact table may contain 300,000+ rows. So, I thought of providing any level of groupid to the query to retrieve just its children and its corresponding aggregated value. Any better solution?

Answer 1

This solution produces the correct result. As long as indexes are in place, this should perform OK against your production dataset, but I haven't tested it on any more than your sample data.

DECLARE @tree TABLE
(ID INT
,name VARCHAR(15)
,ParentID INT
,TYPE TINYINT
)

DECLARE @salary TABLE
(ID INT
,Salary INT
)

INSERT @tree
      SELECT 1,'PA',NULL,0
UNION SELECT 2,'Pittsburgh',1,1
UNION SELECT 3,'Accounts',2,1
UNION SELECT 4,'Alex',3,2
UNION SELECT 5,'Robin',3,2
UNION SELECT 6,'HR',2,1
UNION SELECT 7,'Robert',6,2


INSERT @salary
      SELECT 4,6000
UNION SELECT 5,5000
UNION SELECT 7,4000


;WITH salaryCTE
AS
(
        SELECT t.*
               ,s.Salary
        FROM      @tree         AS t
        LEFT JOIN @salary       AS s
        ON        s.ID = t.ID
)
,recCTE
AS
(
        SELECT t.ID
               ,CAST(t.name AS VARCHAR(MAX)) AS name
               ,t.ParentID 
               ,ISNULL(t.Salary,0) AS Salary
               ,0 AS LEVEL
               ,CAST(t.ID AS VARCHAR(100)) AS ord
        FROM  salaryCTE   AS t
        WHERE t.ParentID IS NULL

        UNION ALL

        SELECT t.ID
               ,CAST(REPLICATE(' ',r.LEVEL) + t.name AS VARCHAR(MAX)) AS name
               ,t.ParentID
               ,ISNULL(t.Salary,0) AS Salary
               ,r.LEVEL + 1
               ,CAST(r.ord + '|' + CAST(t.ID AS VARCHAR(11)) AS VARCHAR(100)) AS ord
        FROM      salaryCTE     AS t        
        JOIN      recCTE        AS r
        ON        r.ID = t.ParentID
)
SELECT name
       ,salary
FROM (       
        SELECT r1.name
               ,r1.ord
               ,SUM(r2.salary) AS salary

        FROM recCTE AS r1
        LEFT JOIN recCTE AS r2
        ON   r2.ord LIKE r1.ord + '%'
        GROUP BY r1.name,r1.ord 
     ) AS x
ORDER BY ord,name