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.
| Author |
Topic |
|
bubberz
Constraint Violating Yak Guru
289 Posts |
 Posted - 2007-08-13 : 13:51:48
|
| What I'd like to do is use a tree view on some route to help allow users to drill down into their data.For instance, we have a table w/ 100 numbers, and they're nvarchar(100).For simplicity sake, 50 start with D1, and the other 50 start with E1.Under the 50 that start with D1, they are grouped by a dot and some more characters up to the next dot:1. D1.012. D1.023. D1.034. D1.045. D1.05When the page first loads, the 2 nodes would show:D1E1..and if the user clicked D1, they'd see new nodes which took the next chars up to any next "dot" and listed the groupings/nodes like above.1. D1.012. D1.023. D1.034. D1.045. D1.05..and if the user clicked on D1.02, and the data would show like:1. D1.02.012. D1.02.023. D1.02.03...and if the user clicked on D1.02.03, they would see new nodes of:1. D1.02.03.0012. D1.02.03.023. D1.02.03.03B..or something like that. Basically, each time the user clicks the node, we show a "dot" and two more characters...or up to the next "dot".I have the tree view working now, but it's pretty much a one-for-one, and that's not very helpful for this scenario.Thanks! |
|
|
jhermiz
3564 Posts |
Posted - 2007-08-13 : 15:29:14
|
Basically this is a parent child relationship. The roots have ParentID = null, while each child has its own set of ParentID's.Consider the following example using your D1.D1 which seperates into D1.01, D1.02, D1.03 etc. D1.02 seperates into D1.02.01, D1.02.02..etc.So in the table you have the followingParentID | ChildIDNULL D1D1 | D1.01D1 | D1.02D1 | D1.03...D1.02 | D1.02.01D1.02 | D1.02.02D1.02 | D1.02.03...D1.02.01 | D1.02.01.XXetc etc So now you have a logical structure, so you simply fill the tree upto how far down the tree might go. To set parents you simply select the rows where the parentid is null. When they click on the parentid you load the nodes of the children of that parent by passing it what was selected, namely the ParentID. And you continue to do this until you have 0 rows returned.make sense ?Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
 |
|
|
bubberz
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-08-13 : 18:09:05
|
| Hi jhermiz,Our table as it sits right now, has the column (which is a primary key / nvarchar(100)) in question looking like:D1D1.01D1.01.01D1.01.01.02D1.01.01.02.003D1.01.02D1.01.02.02D1.01.02.03E1E1.01E1.01.01E1.01.01.02.003E1.01.02E1.01.02.02E1.01.02.033E1.01.02.04So, I'm thinking I have to look for the first "dot" and pull out the characters before that for the first node....then look for the 2nd "dot" and pull every thing before that for the second drill down node. Does that make sense? I think there's a strip or some function to do that, but I'm a bit stumped right now.Any suggestions are welcome!Thanks! |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-08-15 : 00:30:14
|
| Why cant you make relationships out of this table? Can you not add additional tables or modify the existing system? Your existing table does not follow 3NF form. When you start noticing that you have to use SUBSTRINGs, INSTR's, LEFT, RIGHT, MID, UPPER, LOWER, all these functions in your db code or even your front end code then something is wrong. Read about the Third Normal Form and think about your current setup. After all a database is used to create relationships in your data. My example gave you a very simple way to relate a child to a parent. Your code would be much simplier.As for how you do it with what you currently have, I don't know since I would not do it that way. Sorry.Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
|
|
|
|
|
treeview w/ drill down/into data