Converting a parent-child table into a genrational table / text file

I need to convert a parent - child table into another table or a text file containing in a generational format.

eg.

child / parent / grand parent / great grand parent / ....

Does anyone have a stored procedure of code to do this?

I'm working with a dimension having 250,000 + members, writing code is fast enough for much smaller hierarchies but with a dimension this size we need something fast.

Thank you

Hi Rod,

Not sure if this is the same problem as you posted in TSQL under "Adjacency List' - as suggested there, recursive CTE would be one approach in SQL Server 2005:

>>

with GenTable(LeafKey, LeafName, GenNum, AncestorKey, AncestorName) as

(select do.OrganizationKey as LeafKey, do.OrganizationName as LeafName,

1 as GenNum, do1.OrganizationKey as AncestorKey,

do1.OrganizationName as AncestorName

from dbo.DimOrganization do

join dbo.DimOrganization do1

on do.ParentOrganizationKey = do1.OrganizationKey

where not exists(select *

from dbo.DimOrganization do2

where do2.ParentOrganizationKey = do.OrganizationKey)

union all

select gt.LeafKey, gt.LeafName,

gt.GenNum + 1 as GenNum, do.ParentOrganizationKey as AncestorKey,

do1.OrganizationName as AncestorName

from GenTable gt

join dbo.DimOrganization do

on gt.AncestorKey = do.OrganizationKey

join dbo.DimOrganization do1

on do.ParentOrganizationKey = do1.OrganizationKey)

select LeafKey, [1] as Gen1Key, [2] as Gen2Key, [3] as Gen3Key

from (select LeafKey, GenNum, AncestorKey

from GenTable) gt

Pivot (Max(AncestorKey)

for GenNum in ([1], [2], [3])) as pt

order by LeafKey

--

3 14 2 1
4 14 2 1
5 14 2 1
6 14 2 1
7 14 2 1
8 2 1 NULL
11 9 1 NULL
12 9 1 NULL
13 10 1 NULL

>>

|||Hi Deepak,

Thank you very much, it's been as long day and I'll look closer in the morning. It looks vey helpful. I should of mentioned that I have to do the with 2000 and 2005 but this likes I'm half way there and on the right track

Thanks again,

Rod