I got a table tblsid like:
fksid,name….
and a table tblsidgroup like:
fksidparent, fksid
I need a list of users and their parent like:
fksid, fksidparents
i.e.
in tblsid
fksid name
22 admin
23 admins
24 editors
in tblsidgroup
fksidparent fksid
23 22
24 22
the result I want is
fksid fksidparents
22 23,24
A quick list function is using cursor like this one.
create table #t
(
fksid int,
parentid varchar(4000) default ”,
parentname varchar(4000) default ”
)
set nocount on
insert into #t (fksid) select distinct fksid from tblsidgroup
declare @fksid int
DECLARE t_cursor CURSOR FOR
SELECT fksid FROM #t
OPEN t_cursor
FETCH NEXT FROM t_cursor into @fksid
WHILE @@FETCH_STATUS = 0
BEGIN
declare @pid int
declare @pname varchar(255)
declare s_cursor CURSOR FOR select fksidparent,name from tblsidgroup inner join tblsid on ( tblsid.pkid=tblsidgroup.fksidparent) where tblsidgroup.fksid=@fksid
open s_cursor
fetch next from s_cursor into @pid,@pname
while @@fetch_status=0
begin
update #t set parentid=parentid+’,’+cast(@pid as varchar(5)),parentname=parentname+’,’+@pname where fksid=@fksid
fetch next from s_cursor into @pid,@pname
end
close s_cursor
deallocate s_cursor
FETCH NEXT FROM t_cursor into @fksid
END
CLOSE t_cursor
DEALLOCATE t_cursor
set nocount off
select * from #t
drop table #t
I just wonder if their is any other way..
Leave a comment