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
 Transact-SQL (2000)
 Displaying a Tree in Order
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/13/2006 :  09:03:30  Show Profile  Reply with Quote
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

Edited by - Kristen on 11/13/2006 11:36:52

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/13/2006 :  09:11:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SQL2000 or SQL2005?
With 2005, you might be helped with a recursive CTE.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/13/2006 :  09:26:10  Show Profile  Reply with Quote
Thanks. Sadly SQL 2000.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/13/2006 :  09:27:04  Show Profile  Visit spirit1's Homepage  Reply with Quote
i suppose you already read this?
http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html

peter:
Kristen will be using SS2k5 around 2009... not before.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/13/2006 :  09:52:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>Kristen will be using SS2k5 around 2009... not before

Because TESTing is not finished

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/13/2006 :  09:57:20  Show Profile  Reply with Quote
"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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/13/2006 :  10:04:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
care to place a wager on that?



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/13/2006 :  10:12:18  Show Profile  Reply with Quote
How about this reference found in FAQ http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Trees


KH

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/13/2006 :  11:36:27  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/13/2006 :  14:58:31  Show Profile  Reply with Quote
Would you be in the market for a derived column?

I mean it would cost you some up front work, but once it was set up, you could use simple set based sql



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



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/13/2006 :  15:22:29  Show Profile  Reply with Quote
"Would you be in the market for a derived column?"

Yes please. What did you have in mind?

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/13/2006 :  16:39:10  Show Profile  Reply with Quote
OK, I offer this

http://weblogs.sqlteam.com/brettk/archive/2006/11/13/23995.aspx



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



Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/14/2006 :  08:06:29  Show Profile  Reply with Quote
What does "to order the records into display-order" mean?

Jay White
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/14/2006 :  08:47:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/14/2006 :  08:58:07  Show Profile  Reply with Quote
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



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/14/2006 :  09:02:33  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/14/2006 :  11:02:57  Show Profile  Reply with Quote
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



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/14/2006 :  11:05:31  Show Profile  Reply with Quote
EDIT: Oh and doing what you are looking for...the syntax would be something like


SELECT codex, SPACE(LEN(codex)-5)+Employee FROM myPositions99 ORDER BY codex
GO



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



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/14/2006 :  11:11:48  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/14/2006 :  12:00:38  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Maybe that's a pipe dream though.



No, it's not...I'm working on that part now...almost done

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



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/14/2006 :  12:04:19  Show Profile  Reply with Quote
I'll wait with baited breath then!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.14 seconds. Powered By: Snitz Forums 2000