Recursive joins? - 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

SQL Server Development (2000)

Recursive joins?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-03-02 : 15:27:52

Say I've got a table that joins itself:


CREATE TABLE categories (
i INT IDENTITY,
i_categories INT,
name VARCHAR(25))

Is there any better way to get a comma delimited list of parents for any given category than


CREATE FUNCTION dbo.f_GetParents(@iCat int)
RETURNS varchar(500) AS  
BEGIN 
DECLARE @ParentList varchar(300),@iParent int

SELECT @iParent=i_categories FROM categories WHERE i=@iCat
IF @iParent IS NOT NULL
    SELECT @ParentList=dbo.f_GetParents(@iParent) + ', ' + name FROM categories WHERE i=@iParent
ELSE
  SELECT @ParentList=''

RETURN @ParentList
END

Any way to just do it in a query rather than the recursive function?

Thanks
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-03-07 : 00:03:28

Hope you all don't mind I'm pushing this back up -- I've gone ahead and implemented it this way, but it still feels like a procedural solution to a set based problem.

Cheers
-b

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-07 : 00:18:55

Aiken,

I like to use the update statement for creating CSV lists. T-SQL update extension lets you create pseudo identity fields, running totals, and CSV list.

The basic syntax is something like this:
update mytable
set @localvar = columnvar = @localvar + ',' + isnull(columnvar,'')
where columnvar2='my value'

It will create a running "concatenate" from the top row to the bottom row. You can take the last row, which is what you want. Or you can introduce a running counter and take the row with the max counter etc...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources