Recursion Query to get Children and children of children using With

Question

Post reply

Recursion Query to get Children and children of children using With

tusharsn

Old Hand

Points: 314
More actions
July 5, 2011 at 7:41 pm

#240438

Hi All,
Here is my problem .
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
I tried this using ;with in sql 2008 but could not get the above mention result , where if i select parent i will get the output as its children and its grandchildren till the leaf level.
Can anyone me to write the query, I m trying it from 3,4 days but no result.
I want this query asap..
thanks in advance
Tushar

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

swaroop.sahoo SSChasing Mays Points: 617 More actions · Answer 1

swaroop.sahoo

SSChasing Mays

Points: 617

July 5, 2011 at 10:12 pm

#1348727

Can you use while loop?

wjvesschoten SSC Enthusiast Points: 153 More actions · Answer 2

wjvesschoten

SSC Enthusiast

Points: 153

July 6, 2011 at 12:42 am

#1348758

You can use a recursive CTE.

tusharsn Old Hand Points: 314 More actions · Answer 3

tusharsn

Old Hand

Points: 314

July 6, 2011 at 7:33 am

#1348951

I have used CtE but it did not work

Lowell SSC Guru Points: 323518 More actions · Answer 4

here's a recursive CTE example i keep in my snippets; if you can provide the CREATE TABLE/INSERT INTO statemetns like this example, we could help you with yours, but this makes a nice model/starting point regardless:

create table #sample(sno int identity,student_no int, head int,task varchar(50))

insert into #sample (student_no,head,task) values(1,10,'tactical')

insert into #sample (student_no,head,task) values(10,20,'basket')

insert into #sample (student_no,head,task) values(20,40,'aerospace')

insert into #sample (student_no,head,task) values(40,10,'robot')

insert into #sample (student_no,head,task) values(10,40,'tackle')

insert into #sample (student_no,head,task) values(40,60,'trick')

--only works as hierarchy if the child has higher # than parent.

;WITH rCTE AS (

SELECT sno, student_no, head, task, 0 AS [Level]

FROM #sample

WHERE student_no = 1

UNION ALL

SELECT c.sno, c.student_no, c.head, c.task, p.[level] + 1

FROM #sample c

INNER JOIN rCTE p ON p.head = c.student_no AND p.sno < c.sno

)

SELECT DISTINCT sno, student_no, head, task

FROM rCTE

WHERE [Level] BETWEEN 1 AND 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 5

I added some more sample data for testing purposes. This seems to handle what you want it to do. Please note that recursive CTEs are not necessarily the best performing way of doing things

DECLARE @Mappings TABLE (Code VARCHAR(10), Parent_code VARCHAR(10), POSITION VARCHAR(10));

INSERT INTO @Mappings

VALUES ('H1', NULL, 'Root'),

('H11', 'H1', 'Parent'),

('H111', 'H11', 'Parent'),

('H1111', 'H111', 'Leaf'),

('H1112', 'H111', 'Leaf'),

('L11', 'H1', 'Parent'),

('L111', 'L11', 'Parent'),

('L1111', 'L111', 'Leaf');

--One more table which stores amount for leaf level code

DECLARE @Leafs TABLE (Code VARCHAR(10), Amount INT);

INSERT INTO @Leafs

VALUES ('H1111', 100),

('H1112', 200),

('L1111', 500);

DECLARE @StartCode VARCHAR(10) = 'H1';

WITH cte AS

(

SELECT m.Code, Amount = ISNULL(l.Amount,0), m.Parent_code, Hierarchy = CONVERT(VARCHAR(4000), '.' + m.code + '.')

FROM @Mappings m

LEFT JOIN @Leafs l

ON m.Code = l.Code

), cte2 AS

(

SELECT cte.Code,

cte.Amount,

cte.Parent_code,

Hierarchy = CONVERT(VARCHAR(4000), cte.Hierarchy),

Lvl = 1

FROM cte

WHERE cte.Code = @StartCode

UNION ALL

SELECT cte.Code,

cte.Amount + cte2.Amount,

cte2.Code ,

CONVERT(VARCHAR(4000), cte2.Hierarchy + cte.Code + '.' ),

cte2.Lvl + 1

FROM cte2

JOIN cte

ON cte.Parent_code = cte2.Code

)

SELECT c.*

, Lvl.LvlSum

FROM cte2 c

CROSS APPLY (SELECT LvlSum = SUM(Amount)

FROM cte2

WHERE cte2.Hierarchy LIKE '%.' + c.Code + '.%') Lvl

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

tusharsn Old Hand Points: 314 More actions · Answer 6

thx WayneS.

I m using almost same query. But my main problem is as below

If i do

Select * from table

where parent_code= 'H1' * H1 is root

The output should have all the Code under H1, Meaning its Immediate children and its grand children till the leaf level.

It should return its childrend and grand children

H1

H11

L11

L111

L1111

H111

H1111

H1112

and its amounts.

If i select

Parnt_code as H11, it should return

H11

H111

H1111

H1112

And amount corresponding to it.

tusharsn Old Hand Points: 314 More actions · Answer 7

Create TABLE mapping(Code VARCHAR(10), Parent_code VARCHAR(10), POSITION VARCHAR(10));

INSERT INTO Mappings

VALUES ('H1', NULL, 'Root'),

('H11', 'H1', 'Parent'),

('H111', 'H11', 'Parent'),

('H1111', 'H111', 'Leaf'),

('H1112', 'H111', 'Leaf'),

('H2', 'Null', 'Root'),

('H22', 'H2', 'Parent'),

('H222', 'H22', 'Parent');

('H2221', 'H222', 'Leaf');

--One more table which stores amount for leaf level code

Create TABLE Amounts (Code VARCHAR(10), Amount INT);

INSERT INTO Amounts

VALUES ('H1111', 100),

('H1112', 200),

('H2221', 500);

1. I want that all my leaf level amount rolled up to its parents and to its Root.(It can be done using CTE and working fine for me )

But now my main problem is if I select any parent (Include root which is also parent) in where clause I should get its children and its grandchildren till I reach to its leaf level.