CTEs and Trees

Question

Post reply

CTEs and Trees

Frédéric BROUARD

Old Hand

Points: 315
More actions
December 25, 2007 at 9:45 pm

#181462

Comments posted to this topic are about the item CTEs and Trees

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

Great code and explanations, but no test data...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

i want to play with this in detail when i have time, but we had a problem with an app with similar conditions

we have a table with data and need to return it in hierchial fashion. someone wrote and sp years ago that used a while loop and temp tables. i just rewrote it with a CTE and it seems to run a lot faster

Frédéric BROUARD Old Hand Points: 315 More actions · Answer 3

I make some tests some years ago with different methods :

-> tree with classic father/son auto reference (in fac my computer file system including the 4 disks I have)

a) using temp table and a store proc to find some files having '%toto%' in the file name

b) using a path stored with the data in a column NAVARCHAR(4000)

c) using interval model

d) using CTE

Interval model was the winner...

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

what about adding a number column to the master table and using that in your loop?

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 5

Tech_Newbie (6/12/2008)
help please.
I have the following situation:
I want to populate two related tables
Person (PersonID, PersonName, PersonLName)
PersonAddress(PersonAddressID, PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County)
from a table which contains all the data needed
MasterList(PersonName, PersonLName, PersonAddr, PersonCity, PersonState, Personzip, County)
The thing is I am not doing this sequentially, I want to select every 7th person starting from the person in the MasterList table at position 3.
For example:
I have 100 people in the master list
Lets say that I want 30 people to be put into the Persons table and into thier associated address tables.
I want to be able to increment through the MasterList table starting at the person from position 3, and insert every 7th person thereafter into the tables.
If I get to the end of the table and I have not reached my 30 people, I need to loop back around starting at a new position - 2 and then repeat selecting every 7th person from then on out.
Important things:
1. PersonID is an identity, and an auto - incremented value, but within this loop, when I create my entry for Person, I want to turn around and create an entry for PersonAddress, using the newly created PersonID identity value as a foreign key inside of PersonAddress.
How should I write a cte to be able to acomplish this ? Thanks for any assistance provided

Ok... third time's the charm... post your request in the correct forum... you'll get better answers quicker.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)