user and their groups

10 08 2007

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..

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: