DECLARE    @table TABLE 
	(
		unit_code		TINYINT
		, parent_code	TINYINT
		, headcount		INT
		, PRIMARY KEY CLUSTERED (unit_code)
	)

INSERT INTO @table (unit_code, parent_code, headcount)
SELECT    1, 4, 10 UNION ALL
SELECT    2, 4, 130 UNION ALL
SELECT    3, 10, 93 UNION ALL
SELECT    4, 10, 7 UNION ALL
SELECT    10, NULL, 1

unit_code	cumulative_headcount
------------------------------------
1		10
2		130
3		93
4		147
10		241

-- Structure and data
create table MyTable
	(
		unit_code		TINYINT
		, parent_code	TINYINT
		, headcount		INT
		, PRIMARY KEY CLUSTERED (unit_code)
	)

INSERT INTO MyTable (unit_code, parent_code, headcount)
SELECT    1, 4, 10 UNION ALL
SELECT    2, 4, 130 UNION ALL
SELECT    3, 10, 93 UNION ALL
SELECT    4, 10, 7 UNION ALL
SELECT    10, NULL, 1
go

-- Function
create function dbo.AncestorPath(@unit_code int) returns varchar(100) as
begin
    declare @Path varchar(100)
    while 0 = 0
    begin
        select @Path = cast(unit_code as varchar(5)) + isnull('/' + @Path, ''), @unit_code = parent_code
        from dbo.MyTable where unit_code = @unit_code

        if @@rowcount = 0 break
    end

    return @Path
end
go

-- Calculation
select unit_code, (select sum(headcount) from MyTable where '/' + dbo.AncestorPath(unit_code) + '/'
                        like '%/' + cast(a.unit_code as varchar(10)) + '/%') as cumulative_headcount
from MyTable a

/* Results
unit_code cumulative_headcount
--------- --------------------
1         10
2         130
3         93
4         147
10        241
*/

; WITH a AS (
    SELECT unit_code, parent_code, CAST(unit_code AS VARCHAR(MAX)) AS Path, headcount
    FROM MyTable WHERE parent_code is null
    UNION ALL
    SELECT b.unit_code, b.parent_code, a.Path + '/' + CAST(b.unit_code AS VARCHAR(MAX)), b.headcount
    FROM MyTable b INNER JOIN a ON a.unit_code = b.parent_code
)
SELECT unit_code, (SELECT SUM(headcount) FROM a WHERE '/' + Path + '/'
                        like '%/' + CAST(b.unit_code AS VARCHAR(10)) + '/%') as cumulative_headcount
FROM a b ORDER BY unit_code

quote:

---

Originally posted by RyanRandall

And, as in the first of these links, a recursive CTE solution will be possible.

---

;WITH Yak (unit_code, parent_code, headcount, path)
AS (
	SELECT	unit_code,
		parent_code,
		headcount,
		'/' + cast(unit_code as varchar(max)) + '/'
	FROM	mytable AS ts
	WHERE	parent_code is null

	UNION ALL

	select		t.unit_code,
			t.parent_code,
			t.headcount,
			y.path + cast(t.unit_code as varchar(max)) + '/'
	FROM		Yak AS y
	INNER JOIN	mytable AS t ON t.parent_code = y.unit_code
)

SELECT		p.unit_code,
		SUM(y.headcount)
FROM		Yak AS y
CROSS APPLY	(
			SELECT	unit_code,
				parent_code,
				headcount
			FROM	Yak AS t
			WHERE	y.Path LIKE '%/' + cast(t.unit_code as varchar(12)) + '/%'
		) AS p
group by	p.unit_code