Displaying a Tree in Order - 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

Transact-SQL (2000)

Displaying a Tree in Order

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

Posted - 2006-11-13 : 09:27:04

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

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

Posted - 2006-11-13 : 10:12:18

How about this reference found in FAQ

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Trees

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

Posted - 2006-11-13 : 14:58:31

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

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

Posted - 2006-11-13 : 16:39:10

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

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

Posted - 2006-11-14 : 11:05:31

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

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

Posted - 2006-11-14 : 12:00:38

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

Kristen
Test

22859 Posts

Posted - 2006-11-14 : 12:04:19

I'll wait with baited breath then!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources