create id's recursive

• ralf.lulay

  SSC Rookie

  Points: 37

  More actions

  October 29, 2010 at 9:36 am

  #228571

  Hi all,

  i have to generate id and parent_id with a recursive cte,

  first please execute this code:

  if OBJECT_ID('tempdb..#tab') is not null

  drop table #tab

  create table #tab(empid char(1), mgrid char(1));

  insert into #tab

  select 'A',null union all

  select 'B','A' union all

  select 'C','A' union all

  select 'D','B' union all

  select 'E','B' union all

  select 'F','C' union all

  select 'G','C';

  with rec as

  (

  select null as id,

  null as parentid,

  mgrid, empid , cast(empid as varchar(100)) as vector

  from #tab

  where mgrid is null

  union all

  select r.id,

  r.parentid,

  t.mgrid, t.empid , cast(r.Vector + ' > ' + t.empid as varchar(100)) as Vector

  from #tab t

  inner join rec r

  on t.mgrid = r.empid

  )

  select * from rec;

  as you see, id and parentid's are null.

  I would like to achieve the following:

  id |parentid|mgrid|empid|vector

  ------------------------------------------------------------------------------------

  1|NULL|NULL|A|A

  2|1|A|B|A > B

  3|1|A|C|A > C

  4|3|C|F|A > C > F

  5|3|C|G|A > C > G

  6|2|B|D|A > B > D

  7|2|B|E|A > B > E

  it seems to be easy but i have no clue, all i get is

  id |parentid|mgrid|empid|vector

  ----------------------------------------------------------------------------------------

  1|0|NULL|A|A

  2|1|A|B|A > B

  2|1|A|C|A > C

  3|2|C|F|A > C > F

  3|2|C|G|A > C > G

  3|2|B|D|A > B > D

  3|2|B|E|A > B > E

  which is wrong, because id is not unique

  how to solve this? any ideas?

  thank you!

  Ralf

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  October 29, 2010 at 2:41 pm

  #1244433

  Try this:

  ;with EmployeeIds as

  -- Assign Id sequentially using Rank

  (SELECTempid

  ,RANK() OVER ( ORDER BY EmpId) as Id

  FROM#tab

  )

  ,AllIds as

  -- For all of the Manager Ids, get the assigned Rank

  (select#tab.empid

  ,#tab.mgrid

  ,EmployeeIds.Idas Id

  ,MgrIds.Idas parentid

  from#tab

  JOINEmployeeIds

  on EmployeeIds.empid = #tab.empid

  LEFT OUTER JOIN

  EmployeeIdsas MgrIds

  on MgrIds.empid = #tab.mgrid

  )

  ,Vectors as

  -- Recursive: Start with Employee who has no Manager

  (select empid

  ,mgrid

  ,Id

  ,parentid

  ,CAST(empid as varchar(100) ) as vector

  FROMAllIds

  WHEREmgrid is null

  UNION ALL

  -- Recursive

  selectAllIds.empid

  ,AllIds.mgrid

  ,AllIds.Id

  ,AllIds.parentid

  ,cast(Vectors.Vector + ' > ' + AllIds.empid as varchar(100)) as Vector

  FROMAllIds

  JOINVectors

  on AllIds.mgrid = Vectors.empid

  )

  SELECT*

  FROMVectors

  order by vector -- ?

  ;

  SQL = Scarcely Qualifies as a Language

• ralf.lulay

  SSC Rookie

  Points: 37

  More actions

  November 1, 2010 at 2:14 am

  #1244724

  Hi Carl, building the emp id's in advance seems to be the key.

  thank you very much for your help

  kind regards

  Ralf