SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Data Model for "outline" format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

webguru22
Starting Member

USA
11 Posts

Posted - 12/06/2000 :  17:50:45  Show Profile  Send webguru22 an AOL message  Reply with Quote
Hi SQL gurus,
Here's a twist on the old "threaded message board query" question:

I need to come up with a data modle that will let me have elements in an "outline" like this:

<pre>1.0 Top-level element

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.1 Second Level element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.2 Second Level element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.2.1 Third Level element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.2.2 Third Level element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.3 Second Level element
2.0 Top level element
&nbsp;&nbsp;&nbsp;&nbsp;2.1 Second Level element
&nbsp;&nbsp;&nbsp;&nbsp;2.2 Second Level element
&nbsp;&nbsp;&nbsp;&nbsp;2.3 Second Level element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.3.1 Third Level Element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.3.2 Third Level Element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.3.2.1 Fourth Level element
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.3.3 Third Level Element
3.0 Top level element...

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

</pre>

And so on. The catch is, unlike a forum , there is no way to sort the elements by date, and to make it even more fun, I have to be able to select an element, and move it up or down in the heirarchy. So if I move element 2.3 up, it becomes 2.2, and 2.3.1 becomes 2.2.1, etc. And of course 2.2 becomes 2.3, swapping the spots in the heirarchy.

I'm not sure if I made this clear. I appreciate ANY advice. Mostly what I'm looking for is a data model that will et me sort the elements in this way. I can then figure out the logic for moving them up and down, etc.

webguru22
Starting Member

USA
11 Posts

Posted - 12/06/2000 :  17:54:08  Show Profile  Send webguru22 an AOL message  Reply with Quote
Sorry, forgot the HTML is off on this forum. Here's what the outline would look like.

1.0 Top-level element
1.1 Second Level element
1.2 Second Level element
1.2.1 Third Level element
1.2.2 Third Level element
1.3 Second Level element
2.0 Top level element
2.1 Second Level element
2.2 Second Level element
2.3 Second Level element
2.3.1 Third Level Element
2.3.2 Third Level Element
2.3.2.1 Fourth Level element
2.3.3 Third Level Element
3.0 Top level element...


Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 12/06/2000 :  22:57:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
...And they also remove multiple spaces, so your indents are gone too. :)

There's a stored procedure listed in Books Online (search "Expanding Hierarchies") that would help you. It sounds like you are dynamically changing either the parent, and/or the order of parents and children, when you "move" them up or down. Whoa.

Check this post and the additional web sites in it:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=2180&FORUM_ID=5&CAT_ID=3&Topic_Title=Follow+up+on+the+recursive+join+problem&Forum_Title=Developer

Hope this helps. graz also created a link to another page that discusses this:

http://www.sqlteam.com/redir.asp?to=http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15715&ItemID=926

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 12/06/2000 :  23:15:15  Show Profile  Visit graz's Homepage  Reply with Quote
And I actually answered this question too. I've been wanting to write that code for quite a while now. You can read the article here (http://www.sqlteam.com/item.asp?ItemID=1602). It should show up on the site Thursday morning.

[url="http://www.SQLTeam.com"]SQLTeam.com[/url]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000