Recursion Query to get Children and its grandchildren

I have one mapping table:

Co开发者_JAVA技巧de     Parent_code   Position
--------------------------------
H1       Null          Root
H11      H1            Parent
H111     H11           Parent
H1111    H111          Leaf
H1112    H111          Leaf 

One more table which stores amount for leaf level code

Code   Amount
-------------
H1111  100
H1112  200

i.e amount is stored at only leaf position

I want to write the query through which the data at leaf level will get rolled up to its parents and ultimately to its root.

Output will look like below

Code   Amount
-------------
H1     300
H11    300
H111   300
H1111  100
H1112  200

Also if I select H1 that is root then output should be its children and its grandchildren. Same if I select H11 i should get the output as H111 And children of H111

a recursive common table expression should be able to give you the data you need. A good question/answer on this site is here.

A quick example that may help you is this:

create table #Code
(
Code varchar(20),
Parent_Code varchar(20)
)
go
insert into #Code (Code, Parent_Code)
select 'H1', null
union
select 'H11', 'H1'
union
select 'H111', 'H11'
union
select 'H1111', 'H111'
union
select 'H1112', 'H111'
union
select 'H12', 'H1'
union
select 'H121', 'H12'
go
create table #CodeAmount
(
Code varchar(20),
Amount decimal
)
go
insert into #CodeAmount (Code, Amount)
select 'H1111', 100
union
select 'H1112', 200
union
select 'H121', 50

go

with CodeAmountRollup(Code, Parent_Code, Amount)
as
(
    select c.Code, c.Parent_Code, ISNULL(ca.Amount, 0) as Amount from #Code c inner join #CodeAmount ca on c.Code = ca.Code
    union all
    select c.Code, c.Parent_Code, Amount as Amount from #Code c inner join CodeAmountRollup car on c.Code = car.Parent_Code     
)
--select * from CodeAmountRollup
select Code, sum(Amount) as Amount from CodeAmountRollup group by Code

Below is an example of some SQL I wrote recently for a similar scenario, where I need to return all licensee and ordered by the level of licensee. Hopeful this might explain the concept.

WITH LicenseeEntity (iLicenseeId, vcLicenseeName, vcTradingName,iLicenseeType,iLicenseeStatus, iOwnerLicenseeId, Level)
    AS
    (
        -- Anchor Licensee definition
        SELECT l.iLicenseeId, l.vcLicenseeName, 
               l.vcTradingName,l.iLicenseeType,l.iLicenseeStatus, 
               l.iOwnerLicenseeId, 1 AS Level
        FROM Licensee (nolock) AS l
        WHERE iOwnerLicenseeId IS NULL

        UNION ALL
        SELECT l.iLicenseeId, l.vcLicenseeName, 
               l.vcTradingName,l.iLicenseeType,l.iLicenseeStatus, 
               l.iOwnerLicenseeId, 1 AS Level + 1
        FROM Licensee (nolock) AS l
        INNER JOIN LicenseeEntity AS le ON l.iOwnerLicenseeId = le.iLicenseeId
    )

    SELECT *  FROM LicenseeEntity le