Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 11th, 2008, 03:28 PM
Registered User
 
Join Date: Nov 2007
Location: Chicago, IL, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Recursive Query for Formatted Output

Hello All,

if this has already been addressed please forward me the forum post.
----------
Scenario:
Database Schema
ID ParentID Name
1 0 Root1
2 0 Root2
3 1 Root1_Child1
4 1 Root1_Child2
5 2 Root2_Child1
6 3 Root1_Child1_SubChild1
7 3 Root1_Child1_SubChild2

A select All would output as follows

ID ParentID FormattedPath
1 0 0/Root1
2 0 0/Root2
3 1 0/Root1/Root1_Child1
4 1 0/Root1/Root1_Child2
5 2 0/Root2/Root2_Child1
6 3 0/Root1/Root1_Child1/Root1_Child1_SubChild1
7 3 0/Root1/Root1_Child1/Root1_Child1_SubChild2

i used strings for the Formatted path just so you can see a better visual, i will end up using the ID. for example instead of the formatted output of '0/Root2/Root2_Child1' i will need '0/2/5'

thank you in advance for the help

Reply With Quote
  #2 (permalink)  
Old June 11th, 2008, 06:36 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, actually the right answer is to *STORE* your data in the form you are showing, or nearly so.

I do it like this:
Code:
ID  ParentID  Path             Name
1   0         0001             Root1
2   0         0002             Root2
3   1         0001-0003        Root1_Child1
4   1         0001-0004        Root1_Child2
5   2         0002-0005        Root2_Child1
6   3         0001-0003-0006   Root1_Child1_SubChild1
7   3         0001-0003-0007   Root1_Child1_SubChild2
That is, PATH is a VARCHAR that uses "padded" ids of fixed length. And so now, if you do an ORDER BY PATH you get

Code:
ID  ParentID  Path             Name
1   0         0001             Root1
3   1         0001-0003        Root1_Child1
4   1         0001-0004        Root1_Child2
6   3         0001-0003-0006   Root1_Child1_SubChild1
7   3         0001-0003-0007   Root1_Child1_SubChild2
2   0         0002             Root2
5   2         0002-0005        Root2_Child1
Ideal for building "tree" structures. And building the PATH field as you create the table is going to get you the best possible performance, later.

But starting from the other end is tougher...

SQL Server doesn't do well with recursion, though SQL Server 2005 has some new support for it that I haven't tried yet.

If you *KNOW* the maximum "depth" of your tree, you could clearly build this in a single query using a UNION and a SELECT for each "depth".

Otherwise, I think you'd need to use a Stored Procedure, cursors, a temporary table, etc.
Reply With Quote
  #3 (permalink)  
Old June 11th, 2008, 06:38 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

OOPS! Minor goof...got the ORDER BY PATH wrong:

Should of course be
Code:
ID  ParentID  Path             Name
1   0         0001             Root1
3   1         0001-0003        Root1_Child1
6   3         0001-0003-0006   Root1_Child1_SubChild1
7   3         0001-0003-0007   Root1_Child1_SubChild2
4   1         0001-0004        Root1_Child2
2   0         0002             Root2
5   2         0002-0005        Root2_Child1
Reply With Quote
  #4 (permalink)  
Old June 11th, 2008, 07:51 PM
Registered User
 
Join Date: Nov 2007
Location: Chicago, IL, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i see, well, if i do know the depth, lets say only depth of 2, can you provide me an example? thanks for the reply btw

Reply With Quote
  #5 (permalink)  
Old June 11th, 2008, 08:38 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, to get literally what you asked for:
Code:
SELECT id, parentid, name AS path, name 
FROM demo 
WHERE parentid = 0
     UNION
SELECT D2.id, D2.parentid, D1.name + '/' + D2.name, D2.name
FROM demo AS D1, demo AS D2
WHERE D2.parentid = D1.id AND D1.parentid = 0
     UNION
SELECT D3.id, D3.parentid, D1.name + '/' + D2.name + '/' + D3.name, D3.name
FROM demo AS D1, demo AS D2, demo AS D3
WHERE D3.parentid = D2.id AND D2.parentid = D1.id AND D1.parentid = 0
ORDER BY id;
Here's what I mean by "path" and what I think you mean, as well:
Code:
SELECT id, parentid, RIGHT('0000' + CSTR(id),4) AS path, name 
FROM demo 
WHERE parentid = 0
    UNION
SELECT D2.id, D2.parentid,RIGHT('0000'+CSTR(D1.id),4)+':'+RIGHT('0000'+CSTR(D2.id),4), D2.name
FROM demo AS D1, demo AS D2
WHERE D2.parentid = D1.id AND D1.parentid = 0
    UNION 
SELECT D3.id, D3.parentid,RIGHT('0000'+CSTR(D1.id),4)+':'+RIGHT('0000'+CSTR(D2.id),4)+':'+RIGHT('0000'+CSTR(D3.id),4), D3.name
FROM demo AS D1, demo AS D2, demo AS D3
WHERE D3.parentid = D2.id AND D2.parentid = D1.id AND D1.parentid = 0
ORDER BY path;
That use of
    CSTR(...)
is Access, which is how I tested this. In SQL Server, you would change
    CSTR(...)
to
    CONVERT(VARCHAR(100),...)
Reply With Quote
  #6 (permalink)  
Old June 11th, 2008, 08:41 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

That last query produces this output (your data, except names changed/simplified a tad...sorry):
Code:
id   parentid path              name
1    0        0001              root1
3    1        0001:0003         r1, child 1
6    3        0001:0003:0006    r1, c1, grandkid 1
7    3        0001:0003:0007    r1, c1, grandkid2
4    1        0001:0004         r1, child 2
2    0        0002              root2
5    2        0002:0005         r2, child 1
Reply With Quote
  #7 (permalink)  
Old June 11th, 2008, 08:53 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Last comment...

The query looks ugly, but it will actually perform pretty well. I would think you could use up two a depth of 5 or 6 (I call what I showed "depth 3") and get decent performance. Even if it just so happened that your actual data depth is (say) only 4.
Reply With Quote
  #8 (permalink)  
Old June 12th, 2008, 07:54 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Old Pedant-

What happens when you need to move a node of the tree structure? There's a bit of work involved with recomputing child nodes if you are storing discreet paths in each.

SQL Server 2005's Common Table Expression capability is rather good at dealing with recursive structures.

-Peter
compiledthoughts.com
Reply With Quote
  #9 (permalink)  
Old June 12th, 2008, 09:54 AM
Registered User
 
Join Date: Nov 2007
Location: Chicago, IL, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you very much Pedant, i'll give her a try, also thanks Planoie for the Suggestion, i'm going to look into both options actually.



Reply With Quote
  #10 (permalink)  
Old June 12th, 2008, 01:36 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

To Planoie:

Yes, I mentioned that SQL Server 2005 had recursion capabilities that I hadn't yet used.

Granted, using a "path" is much better used for read-only data. But there are tons of examples of such data in the real world. For example, forum posts. (Well, if the forum code is smarter than this mildly braindead one, such that you can reply to a particular message instead of just to the topic/thread.)

Anyway, he doesn't have a path, just the it/parentid relationship. So if you could show him how to produce his desired output using SQL Server 2005 recursion, it would be appreciated, I'm sure!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP to query a SQL recursive tree chame Classic ASP Basics 4 April 10th, 2012 07:59 PM
Help with a recursive Query!!! dbayona SQL Server 2005 1 December 20th, 2007 06:40 AM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 12:49 AM
recursive query cathiec SQL Language 2 August 25th, 2006 05:58 AM
recursive transform output different in IE and FF seamus7 XSLT 2 January 20th, 2006 05:01 PM



All times are GMT -4. The time now is 04:02 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.