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

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

• dutchtrader14

  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
  
  

• DMDJ

  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

  >>

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