Help with SQL Query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with SQL Query...

Author

Topic

samadhi
Starting Member

1 Post

Posted - 2003-07-22 : 00:27:18

Hi all,

I've got a table named Users having three columns...Name,UserId and ManagerId.

Each Manager can have one or more users working under him and then each user might be manager to one or more users.

I need to fire an sql query to display a tree like structure of manager,his subordinates,the subordinate's subordinates and so on.

I'm using .Net and sql server 2000.

I need to populate the treeview web control in asp.net with the data as above(preferably i need to generate the data from the database in xml format so that i can give that xml as datasource to the Treeview web control in the front-end.).

Could anyone please help me out with this...???

Thanks in advance...

Rahul Kumar Saxena

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-22 : 10:08:21

Ahhh...the hierarchy question again

How many levels deep do you have?

There are several ways to do this...

But check out Rob's article:

http://www.sqlteam.com/item.asp?ItemID=8866

Ahh, where's part 2 again?

I couldn't crack it...

Just used my old refresh everything model...

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-23 : 10:54:48

(a profane drool after reading rob's article (Part I))

CREATE TABLE [a] (
[lev] [int] NULL ,
[p] [varchar] (5) NOT NULL ,
[c] [varchar] (5) NOT NULL ,
CONSTRAINT [PK_a] PRIMARY KEY NONCLUSTERED
(
[p],
[c]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [aa] (
[p] [varchar] (5) NOT NULL ,
[nm] [varchar] (5) NULL ,
CONSTRAINT [PK_aa] PRIMARY KEY NONCLUSTERED
(
[p]
) ON [PRIMARY]
) ON [PRIMARY]
GO


p - parent    c - child

--- a ------------              -- aa ------
lev	p	c                p      nm
------------------              ------------
0	g	b                g	john
0	g	d                b	paul 
0	g	e                d	nick
1	b	y                e	don 
1	b	k                y	mario    
1	b	z                k	rok          
1	d	r                z	floyd 
1	e	q                r      steve     
1	e	m                q	kit     
2	q	o                m	zak    
2	q	w                o	osuo
------------------               w      wess    
		                --------------

=
declare @i int, @p varchar(5), @c varchar(5), @j varchar(8000)
set @i=0 set @j=(select top 1 p from a where lev=0)
set @j='<'+@j+'></'+@j+'>'
while @i<=(select max(lev) from a)
begin
declare abc cursor for select p, c from a where lev=@i
open abc
fetch next from abc into @p, @c
while @@fetch_status=0
begin
set @j=stuff(@j,charindex(@p,@j)+2,0,'<'+@c+'></'+@c+'>')
fetch next from abc into @p, @c
end
close abc
deallocate abc
set @i=@i+1
end

declare abc cursor for select p, nm from aa
open abc
fetch next from abc into @p, @c
while @@fetch_status=0
begin
set @j=stuff(@j,charindex('<'+@p,@j)+3,0,@c)
fetch next from abc into @p, @c
end
close abc
deallocate abc

print @j
==================================================

Output:

<g>john<e>don<m>zak</m><q>kit<w>wess</w><o>osuo</o></q>
</e><d>nick<r>steve</r></d>paul<z>floyd</z><k>rok</k>
<y>mario</y></g>


- <g>
    john
 - <e>
     don
     <m>zak</m>
    - <q>
        kit
        <w>wess</w>
        <o>osuo</o>
      </q>
   </e>
 - <d>
    nick
    <r>steve</r>
   </d>
 - <b>
    paul
    <z>floyd</z>
    <k>rok</k>
    <y>mario</y>
   </b>
 </g>

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-24 : 04:36:15

... or on client's VBA code (from ADP in this case) and avoiding 8000 maxsize
limitation (tables are the same as in my previous post):

Sub myXML()
Dim rs As New ADODB.Recordset
Dim i, maxLEV, k, s, ss
rs.CursorLocation = adUseClient
rs.Open "select * from a order by lev", CurrentProject.Connection, adOpenStatic
s = "<" & rs(1) & "></" & rs(1) & ">"
rs.MoveLast
maxLEV = rs(0)
While i <= maxLEV
rs.MoveFirst
While Not rs.EOF
If rs(0) = i Then
ss = Split(s, "<" & rs(1) & ">", 2, 1)
s = ss(0) & "<" & rs(1) & "><" & rs(2) & "></" & rs(2) & ">" & ss(1)
End If
rs.MoveNext
Wend
i = i + 1
Wend
rs.Close
Debug.Print s

' <g><e><m></m><q><w></w><o></o></q></e><d><r></r></d>
' <z></z><k></k><y></y></g>

rs.Open "select * from aa", CurrentProject.Connection, adOpenStatic
While Not rs.EOF
ss = Split(s, "<" & rs(0) & ">", 2, 1)
s = ss(0) & "<" & rs(0) & ">" & rs(1) & ss(1)
rs.MoveNext
Wend
Debug.Print s

<g>john
<e>don<m>zak</m><q>kit<w>wess</w><o>osuo</o></q></e>
<d>nick<r>steve</r></d>paul<z>floyd</z><k>rok</k><y>mario</y>
</g>

rs.Close
Set rs = Nothing
End Sub

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources