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 |
|
Kristen
Test
22859 Posts |
 Posted - 2006-11-13 : 09:03:30
|
I have some data that is in tree form. It currently contains a Code, Parent (code) and Level.
I don't have a Path (nor an easy way of adding a column to store that, not the necessary logic to maintain a path : this is between releases, we'd need lots of QA to get a new column out into the wild)
So I'm looking for a cost-effective way to order the records into display-order via a temporary table. There is some down-stream caching which will mean that this will only get called when one of the underlying records changes.
Is there any way of avoiding looping round the number of levels that are used?
My sample data is deliberately sorted by Level, Parent code then Code as I was hoping that I might be able to do something clever along the lines of setting the Sequence number of each item to some multiple of the Level and add on the ID number. But if that is possible I certainly can't get my brain around it!
Thanks
Kristen
-- DROP TABLE #CATEGORY
CREATE TABLE #CATEGORY
(
T_ID int IDENTITY(1,1) NOT NULL,
Code varchar(30) NOT NULL,
Parent varchar(30) NULL,
Level int NOT NULL,
Sequence int NULL,
Path varchar(1000) NULL,
PRIMARY KEY
(
Code
)
)
GO
INSERT INTO #CATEGORY(Code, Parent, Level)
SELECT *
FROM
(
-- Code Parent Level
------- -------------------------------- ---- -------------------------------- ---- ----- ---------
SELECT [Code] = 'OUTDOOR KIDS' , [Parent] = NULL , [Level] = 1 UNION ALL
SELECT 'GEAR TO CARRY' , NULL , 1 UNION ALL
SELECT 'GEAR TO SLEEP IN' , NULL , 1 UNION ALL
SELECT 'GEAR TO WEAR' , NULL , 1 UNION ALL
SELECT 'GKTEST' , NULL , 1 UNION ALL
SELECT 'HATS GLOVES MITTS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'HYDRATION PACKS' , 'GEAR TO CARRY' , 2 UNION ALL
SELECT 'MOSQUITO PROTECTION' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'PILLOWS LINERS' , 'GEAR TO SLEEP IN' , 2 UNION ALL
SELECT 'RUCKSACKS' , 'GEAR TO CARRY' , 2 UNION ALL
SELECT 'SKI WEAR' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'SLEEPING BAGS' , 'GEAR TO SLEEP IN' , 2 UNION ALL
SELECT 'SLEEPING MATS' , 'GEAR TO SLEEP IN' , 2 UNION ALL
SELECT 'SOCKS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'SUMMER CLOTHING' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'TORCHES' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'TRAVEL COTS' , 'GEAR TO SLEEP IN' , 2 UNION ALL
SELECT 'TRAVEL ESSENTIALS' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'TREKKING POLES' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'WATERPROOF JACKETS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'WATERPROOF TROUSERS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'BABY CARRIERS' , 'GEAR TO CARRY' , 2 UNION ALL
SELECT 'BASE LAYERS & THERMALS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'BASELAYERS & THERMALS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'BASELAYERS AND THERMALS' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'CLIMBING' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'COMPASSES' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'DAYPACKS' , 'GEAR TO CARRY' , 2 UNION ALL
SELECT 'DOWN CLOTHING' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'DRINKING BOTTLES' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'FIRST AID' , 'OUTDOOR KIDS' , 2 UNION ALL
SELECT 'FLEECE CLOTHING' , 'GEAR TO WEAR' , 2 UNION ALL
SELECT 'FOOTWEAR' , 'GEAR TO WEAR' , 2
) AS X
ORDER BY Level, Parent, Code
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-13 : 09:11:03
|
SQL2000 or SQL2005?
With 2005, you might be helped with a recursive CTE.
Peter Larsson
Helsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 09:26:10
|
Thanks. Sadly SQL 2000.
Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-11-13 : 09:52:12
|
>>Kristen will be using SS2k5 around 2009... not before
Because TESTing is not finished 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 09:57:20
|
"i suppose you already read this?"
I am now 
"Kristen will be using SS2k5 around 2009... not before"
not before, indeed, but I'm not sure about 2009 - feels a bit soon ...
Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-13 : 10:04:08
|
care to place a wager on that? 
Go with the flow & have fun! Else fight the flow 
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 11:36:27
|
I was looking to try to get a Sequence Number only, but I can't see a way to do that, so am going with a Path instead for now. (I have retrospectively added a PATH column to the CREATE TABLE above)
DECLARE @intLoop int,
@intRowCount int
SELECT @intLoop = 1
UPDATE U
SET Path = RIGHT(SPACE(10) + CONVERT(varchar(10), T_ID), 10)
FROM #CATEGORY AS U
WHERE Level = @intLoop
SELECT @intRowCount = @@ROWCOUNT,
@intLoop = @intLoop +1
WHILE @intRowCount > 0
BEGIN
UPDATE C
SET Path = P.Path + RIGHT(SPACE(10) + CONVERT(varchar(10), C.T_ID), 10)
FROM #CATEGORY AS C
JOIN #CATEGORY AS P
ON P.Code = C.Parent
WHERE C.Level = @intLoop
SELECT @intRowCount = @@ROWCOUNT,
@intLoop = @intLoop +1
--DEBUG SELECT [@intLoop]=@intLoop-1, [@intRowCount]=@intRowCount
END
SELECT *
FROM #CATEGORY
ORDER BY Path, T_ID
Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 15:22:29
|
"Would you be in the market for a derived column?"
Yes please. What did you have in mind?
Kristen
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-14 : 08:06:29
|
What does "to order the records into display-order" mean?
Jay White
|
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-11-14 : 08:47:56
|
quote:
Originally posted by Page47
What does "to order the records into display-order" mean?
Jay White
That is the key to the solution needed. If the data is being displayed in a hierarchical control like a tree, or in a crystal report (which supports creating hierarchies) then the solution is:
SELECT * FROM YourTable ORDER BY Level
and that's it. It all depends on ultimately where and how you wish to display the data; that's the a key piece of information needed before a solution can be created.
So what is it, then? A report? Excel? A web page? A windows form? A treeview ? etc ....
- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-11-14 : 08:58:07
|
Well.....
"Level" becomes tricky, especially if you talk in terms of an employee and their job code in relation to their position to the top of a tree
In any case the solution I posted is working well, even though it requires derived data that needs to be maintained
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-14 : 09:02:33
|
I need the records sorted into Parent / Child order; tie-break on PK.
This is so that the web server can present them in the order they are given, because the rendering tool we are using isn't smart enough (yet) to re-organise the data into a the right order - but maybe the right answer, longer term, is to add a feature to the rendering tool and get it to rearrange the rows into "tree order" - a linked-list at the Client end might be all that is needed.
Brett: I haven't forgotten about your post, but it will take some experimentation to see whether that many derived columns is going to help me.
Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-11-14 : 11:02:57
|
Kristen...As David points out the script has problems...it must have been an od one, but I did have one that orked top to bottom...I must have lost it, but I am working on correcting this as we speak...
BUT, basically the concept is
The top parents gets a codex node value of 00001
The next level inherit the paarents node and are assigned their own node, such that
1. 0000100001
2. 0000100002
3. 0000100003
The #1 child who has children would get
1. 000010000100001
2. 000010000100002
3. 000010000100003
The #1 grandchild's children w6ould get
1. 00001000010000100001
2. 00001000010000100002
3. 00001000010000100003
Such that now if you wanted to see someone's entire lineage, you can simplly say
SELECT * FROM table WHERE CODEX LIKE '0000100001%'
The script I'm fixing will discuss, promotions of branches, adding new children, looking for specific # of levels, ect.
It's funny, because I have to revisit this for another segement of a project, and I thought my script with examples was 100%. I had to do this for DB2 ad it's slightly different...in switching gears I must have lost the script...I did this in Feb, and I got a new laptop since then...so it could be a bonehead save to local drive mistake..I know I had it 100% at 1 point
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-14 : 11:11:48
|
It'll be in the Version Control ... eh? 
How many "working" tables do you wind up with - one for each level?
My plan was that I have an IDENTITY column, and that the rows are in ascending Level (so a Level-1 record has a lower ID than all its offspring).
I can (pre-)count the number of rows at each level.
I can update a Sequence column with some mathematical variation of: the Level, total number of possible children, and the difference between the ID and the corresponding ID of the parent etc.
Clearly I can do this in multiple passes, like the method for building a PATH. It may be slightly more efficient than a path, because it will only need an INT rather than a VARCHAR(SomethingBig!)
But it would be a joy if the Sequence could be assigned for all levels in a single Update. Maybe that's a pipe dream though.
Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-14 : 12:04:19
|
| I'll wait with baited breath then! |
|
|
|
Next Page
|
|
|
|
|
Displaying a Tree in Order