Recursive Queries in SQL Server 2005

Question

Post reply

Recursive Queries in SQL Server 2005

Viewing 11 posts - 31 through 40 (of 40 total)

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

natasha.haslam SSC Enthusiast Points: 148 More actions · Answer 1

Satish Jha (5/24/2006)

Thanks for this article. I have one question here -how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order

I have used recursion before using while loop and found a tree code very useful. The tree code allows you to sort the data as if it was displayed on a hierarchical tree view. I used another new CTE function to return incremental identifiers for a portion of data (ie, incremental identifiers for the child row of a certain parent) and staggered them in varchar of length 40 (allowing 20 levels with a max of 99 children).

The formatting was a little tricky. I used the RIGHT and LEFT Functions to pad the numbers with 0s

and I have assumed that you want the ordering of the members of each leave to be by the field DateJoined.

For the anchor member definition I had some similar code for that field...

right('0' + cast(ROW_NUMBER() OVER (Partition BY a.ReportingLevelID ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19)

and for the recursive member definition I used...

left(left(b.TreeCode, (b.TreeLevel+1)*2) + right('0' + cast(ROW_NUMBER() OVER (Partition BY b.TreeLevel ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19),40)

natasha.haslam SSC Enthusiast Points: 148 More actions · Answer 2

MikeAngelastro (4/18/2008)

GSquared (4/18/2008)

MikeAngelastro (3/13/2008)

I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.

Am I wrong in assuming that a CTE cannot handle this situation?

A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.

GSquared,

Thanks for your input.

I did use a CTE initially. But situations arrived later where the resulting record set had too many rows. My tests indicated that the extra rows appear as soon as a branch appears more once in the table; that is, any branch can be a child in more than one product tree. The product-tree I am dealing with has this possibility and therefore I have to handle it. I searched the internet for a sample CTE that was specifically designed to handle this condition and found none.

Because it turned out that the column values in the extra rows appeared to be identical to one of the original rows, I tried to use a “DISTINCT” qualifier but the CTE refused to run, even when I used the following approach:

SELECT DISTINCT FROM CTE

where “CTE” is the CTE’s record set result - extra rows and all.

And even here, when rows have the same values as other rows, it does not necessarily mean they should be excluded from the result; this would happen if the same branch appears more than one in the same overall product tree. Given these results, how can the CTE be constructed in order to exclude the extra rows?

Thanks,

Mike

I am having a similar problem. I originally used a while loop to create the heirachical structure and if a child had been examined before I would restrict the itteration so that the geneology of that child was only shown once.

I used the following code to stop the recursion. (@ChildLotIDs was the heirachical temporary table and consumedlotid was the and the ConsumedLotID was the field that allowed the self join.)

C.ConsumedLotID NOT IN (SELECT EL.ConsumedLotID FROM @ChildLotIDs EL WHERE ClI.ParameterLotID = EL.ParameterLotID AND EL.TreeLevel <= @Level)

However when using CTE I can't reference the table twice in the recursive member defintion..

I get the following error..

Recursive member of a common table expression 'LotGeneology' has multiple recursive references.

Any ideas how to get around this ?

hbpank Right there with Babe Points: 798 More actions · Answer 3

hbpank

Right there with Babe

Points: 798

February 6, 2009 at 2:12 am

#939344

Thanks a lot Shrinivas for that.

I just want to make a Point is that to get same sort of output in the Oracle is quite easier...

Just use START WITH clause specify who would be the Parent and Oracle takes care of all. I do agree that SS 2005 is better but Oracle is still the Best.

Hary Pank

Thanks a lot,
Hary

hbpank Right there with Babe Points: 798 More actions · Answer 4

hbpank

Right there with Babe

Points: 798

February 6, 2009 at 2:24 am

#939345

Thanks a lot Shrinivas for that.

I just want to make Point is that to get results in the Oracle it quite a lot Easier...

Just use START WITH clause specify who would be the Parent and Oracle takes care of all. I do agree that SS 2005 has better but Oracle is still the Best.

Hary Pank

Thanks a lot,
Hary

darren_1065 SSC Rookie Points: 38 More actions · Answer 5

Very good article. However, I couldn't implement this without having an existing Level value. I kept working at this and came up with what appears to be a nice alternative. It's not a recursive procedure but gave me the results I wanted to I thought I'd share my work.

My main table structure is a single table with related entities I'll call departments, where Departments are related to one another.

DepartmentID int,

ParentID int,

Title varchar(25)

What I needed was a procedure that I could pass an ID into and it would give me that ID and all related IDs down the tree. I did this using a temporary table. First inserting the Main record that was passed into the procedure then retrieving all child records using a join back to the temp table. This required using a loop which I did by using a WHILE statement and checking the @@ROWCOUNT var. This looks to work fine for retrieving segments of the table but won't work for getting the entire table, which is simple enough to do with a select statement. Here's the rest.

CREATE PROCEDURE [dbo].[store_GetProductByDepartment]

(

@DepartmentID int

)

AS

-- create temp table for top 2 levels of menu

CREATE TABLE #TEMP_TABLE(

[DepartmentID] [int] NOT NULL,

[ParentID] [int] NULL,

[Title] [nvarchar](256) NULL

)

-- get the first record

INSERT INTO #TEMP_TABLE (DepartmentID, ParentID, Title)

SELECT

d1.DepartmentID

, d1.ParentID

, d1.Title

FROM dbo.store_Department d1

WHERE d1.DepartmentID = @DepartmentID

-- loop through the records and insert found departments

WHILE @@ROWCOUNT > 0

BEGIN

INSERT INTO #TEMP_TABLE (DepartmentID, ParentID, Title)

SELECT

d1.DepartmentID

, d1.ParentID

, d1.Title

FROM dbo.store_Department d1

INNER JOIN #TEMP_TABLE d2 ON d1.ParentID = d2.DepartmentID

WHERE d1.DepartmentID NOT IN (SELECT DepartmentID FROM #TEMP_TABLE)

END

-- testing

-- select * from #TEMP_TABLE

SELECT *

FROM store_Product

WHERE

store_Product.DepartmentID in (SELECT DepartmentID FROM #TEMP_TABLE )

I haven't tested this in all situations. If anyone can see problems from doing this or have any suggestions for improvement please post. Hope this helps.

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 6

Santhoshkumar KB

SSCertifiable

Points: 5066

September 3, 2009 at 8:16 am

#1048769

Hi,

In this article, I need to know what is the purpose of SortKey in CTE.

In the hierarchy, will the Top level row is returned first??

(In our project we need only the top row - so it is important that the row returned is correct)

Please let me know.

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 7

KB (9/3/2009)

Hi,

In this article, I need to know what is the purpose of SortKey in CTE.

In the hierarchy, will the Top level row is returned first??

(In our project we need only the top row - so it is important that the row returned is correct)

Please let me know.

Thanks,

KB

Please dont cross post

http://qa.sqlservercentral.com/Forums/Topic782210-338-1.aspx

Clear Sky SQL
My Blog[/url]

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 8

Dave Ballantyne (9/3/2009)

KB (9/3/2009)

Hi,

In this article, I need to know what is the purpose of SortKey in CTE.

In the hierarchy, will the Top level row is returned first??

(In our project we need only the top row - so it is important that the row returned is correct)

Please let me know.

Thanks,

KB

Please dont cross post

http://qa.sqlservercentral.com/Forums/Topic782210-338-1.aspx

Sorry for that...

I didn't know whether this Discussion is active or not, so I posted here too..

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Tepluken SSC Enthusiast Points: 149 More actions · Answer 9

Tepluken

SSC Enthusiast

Points: 149

October 9, 2009 at 11:03 am

#1064252

Thank you Srinivas. A very lucid, intelligible presentation of CTEs. I Much appreciate your efforts

swoveland21 SSC Journeyman Points: 84 More actions · Answer 10

swoveland21

SSC Journeyman

Points: 84

December 30, 2010 at 12:31 pm

#1267895

Hello, I "Google'd" across this as I was pondering a solution to this problem and think it solves it nicely but it is limited to only 100 recursions. I wonder what some other solutions to the problem might be?

Problem

TSQL Challenge 46 - Remove leading occurrences of the first character in a string

JX Harding Grasshopper Points: 14 More actions · Answer 11

Here is the sample organizational chart.

Chief Executive Officer

Senior Director – Product Development

Product Development Manager

Project Lead

Developers

QA Lead

Testing Team

Documentation Lead

Technical Writers

would it be possible to say:

1. i want to view the hierarchy of managers of technical writers

which would then return :

Chief Executive Officer

Senior Director – Product Development

Product Development Manager

Documentation Lead

(technical writers)?