Recursion, keeping track of first Parent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursion, keeping track of first Parent

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-17 : 18:05:13

I have data like this


DECLARE @Table Table (PolicyNumber char(3) not null ,PreviousPolicyNumber char(3) null)

INSERT INTO @Table(PolicyNumber,PreviousPolicyNumber)
SELECT 'ABC',NULL UNION ALL
SELECT 'DEF','ABC' UNION ALL
SELECT 'FGH','DEF'

I need to get the First policy Number, and all of its children like this
SELECT 'ABC' as PolicyNumber,'ABC' as PreviousPolicyNumber UNION ALL
SELECT 'ABC','DEF' UNION ALL
SELECT 'ABC','FGH'

I have tried this


; With Pols as (
select distinct  		
              PolicyNumber  as Parent 
			, PreviousPolicyNumber  as  Child--PolNums --parent
    
from   @table
 where --PreviousPolicyNumber is null and  
 )

Union All
select
  P.Parent 
 ,C.PolicyNumber  
 
From   @table c 
Inner Join Pols P on  P.Parent = C.PreviousPolicyNumber
) 

--
 select * from pols

Any help would be much appreciated

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-17 : 18:41:01

It's not just this, is it?

; With Pols as (
select distinct
PolicyNumber as Parent
, PolicyNumber as Child--PolNums --parent

from @table
where PreviousPolicyNumber is null

Union All
select
P.Parent
,C.PolicyNumber --+CAST(C.PolicyNumber as varchar(max)) as Child

From @table c
Inner Join Pols P on P.Child = C.PreviousPolicyNumber
)

select * from pols

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-17 : 20:17:44

I was kind of confused by your mixture of policy/prevPolicy vs. Parent/child. But try this:
you can select whichever columns you want out to the final select.


; With Pols (originalPolicyNumber, policyNumber, PreviousPolicyNumber) as
(
	select	PolicyNumber
			,PolicyNumber  
			, PolicyNumber
	from   @table
	where PreviousPolicyNumber is null 

	Union All

	select
		p.originalPolicyNumber
		,c.policyNumber
		,c.PreviousPolicyNumber
	From   Pols p
	join	@table c 
		 on  c.PreviousPolicyNumber = p.policyNumber
		 
) 

select * from pols

OUTPUT:
originalPolicyNumber policyNumber PreviousPolicyNumber
-------------------- ------------ --------------------
ABC                  ABC          ABC
ABC                  DEF          ABC
ABC                  FGH          DEF

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-17 : 21:08:57

Thanks TG!

That is exactly what I needed. One of these days I will understand recursion. But for now, I'm going to have a beer and a smoke and worry about it tomorrow!

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-17 : 21:38:41

you're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources