How to write recursive function in SQL Server ?

I am getting problem in make a function for the following situation.

Suppose i have a table named Opportunity_Duplicate_Tbl

    Opp_ID      Dup_ID                    
R1    111        200                    
R2    111        300                    
R3    111        400                    
R4    222        56                    
R5    300        200                    
R6    101        400                    
R7    112        50                    
R8    100        101                    

R1,R2.... are just row number and not part of actual data. Here we have two cols Opp_ID and Dup_ID. Here 200,300,400 are duplicates of 111. Total Duplicates of 111 are 3 right now. But 200 and 400 are duplicates of 300 and 101 respectively. Think relation like a=b and b=c, then c=a or a=c also. So total duplicates of 111 are 5.As 101 is duplicate of 100 and 400 is duplicate of 101.. we have ommited the R5 row since 200 and 300 are direct duplicates of 111.

So 200,300,400,101 and 100 are total duplicates of 111.
I have solve the problem of getting total number of Dup_ID for each Opp_ID.
Please help me out.
Thanks in advance.

more ▼

asked Oct 22, 2009 at 06:53 AM in Default

Mohammad Traiq gravatar image

Mohammad Traiq
11 1 1 1

What is the desired output for this sample data?
Oct 23, 2009 at 03:25 PM TG
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

Check out the Books Online on recursive CTE http://msdn.microsoft.com/en-us/library/ms186243.aspx

more ▼

answered Oct 30, 2009 at 03:28 AM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Oct 22, 2009 at 07:23 AM

Peso gravatar image

1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 22, 2009 at 06:53 AM

Seen: 3400 times

Last Updated: Oct 29, 2009 at 10:06 AM