Is it possible to utilize Recursive CTE in this example?

Question

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable
create table #MyTable (PolNo varchar(50), ControlNo int, PrevControlNo int, Premim money)
insert into #MyTable values ('Policy-00', 5000, NULL, 1000),
                            ('Policy-01', 6000, 5000, 3200),
                            ('Policy-02', 7000, 6000, 4500),
                            ('Policy-03', 8000, 7000, 3800)
select * from #MyTable

Raw output looks like this:

Using the recursive CTE is it possible to get the result like below?

I tried something like this, but it basically gives me the same result:

;with cte as (
select m.PolNo, m.ControlNo, PrevControlNo, 1 as lvl
from #MyTable m
union all 
select c.PolNo, c.ControlNo, c.PrevControlNo, c.lvl+1
from cte c
inner join #MyTable m ON m.ControlNo = c.PrevControlNo
where c.PrevControlNo IS  NULL
) 
select cte.PolNo,  ControlNo, PrevControlNo
from cte

How are you managing the recursion? In your expected output, Policy-03 (control 8000) has a previous control of 7000, which itself has a previous control of 6000, etc. If you are only worried about the 1 direct previous control, then you can do a simple self join without a CTE. — basodre, Dec 03 '20 at 19:21
Ah, yes, that's right. I guess I overcomplicated everything. Thank you — Serdia, Dec 03 '20 at 19:37

score 1 · Accepted Answer · answered Dec 03 '20 at 19:44

1

LEFT JOIN is enough for single level:

SELECT *
FROM #MyTable m1
LEFT JOIN #MyTable m2
  ON m2.ControlNo = m1.PrevControlNo;

db<>fiddle demo

answered Dec 03 '20 at 19:44

Lukasz Szozda

162,964
23
234
275

Is it possible to utilize Recursive CTE in this example?

1 Answers1