October 29, 2010 at 9:36 am
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
October 29, 2010 at 2:41 pm
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
November 1, 2010 at 2:14 am
Hi Carl, building the emp id's in advance seems to be the key.
thank you very much for your help
kind regards
Ralf
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply