Recursion Query to get Children and its grandchildren

Question

I have one mapping table:

Code     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

Can you upload a small SQL statement that would create a table and load in some sample data? It is very hard to read your text statements — Nat, Jul 06 '11 at 01:55
possible duplicate of [SQL Server recursive query](http://stackoverflow.com/questions/3916597/sql-server-recursive-query) — Scott Weinstein, Jul 06 '11 at 02:15
Not a duplicate as this question requires the code to sum the resulting Amount. Enough of a quirk to differentiate the query — Nat, Jul 06 '11 at 21:23

score 1 · Answer 1 · edited May 23 '17 at 10:27

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

Nickz · Answer 2 · 2011-07-06T03:42:27.693

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

Recursion Query to get Children and its grandchildren

2 Answers2