Steven Willis (7/31/2013)
If you could give us some sample data in table form it would be a lot easier to help you.I could be totally off-base, but my first thought is to use multiple CTEs. I'm not sure that recursion is even necessary. Without the tables or data this is just pseduo-code so run with it.
Thanks for responding but I am not quite sure I get where you are going.
The source data I have is simply a table like the following and I have created a very small bit of data:
CREATE TABLE HouseResident
(
HouseId INT,
ResidentId INT
)
INSERT INTO HouseResident
VALUES(194290,2300365)
INSERT INTO HouseResident
VALUES(194291,2300365)
INSERT INTO HouseResident
VALUES(194291,2300847)
INSERT INTO HouseResident
VALUES(192099,2300847)
INSERT INTO HouseResident
VALUES(192099,2300914)
The result set I need to insert into a table and it should look like the following:
PoolId | HouseId | ResidentId
1 | 194290 | 2300365
1 | 194291 | 2300365
1 | 194291 | 2300874
1 | 192099 | 2300874
1 | 192099 | 2300914
Basically there is a heap of data that I need to pool together like above.
Hope I have made sense?
EDIT > I have just discovered that I need to go more than 4 levels deep.
So I would need a procedure that would "continue to the end" if you get me.
Cheers