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 2005 Forums
 Transact-SQL (2005)
 Recursive call in in SQL?

Author  Topic 

dlouche
Starting Member

9 Posts
Posted - 2008-11-25 : 13:26:46
Is there a way to make a recursive call in in SQL?

I have a table that refers to itself:

ID | ParentID | Name
0 | null | 'first'
1 | 0 | 'second'
2 | 1 | 'third'

and I would like to output of my select to be.

ID | Name | NameChain
0 | 'first' | 'first'
1 | 'second'| 'first, second'
2 | 'third' | 'first, second, third'

Right now I am building the namechain in code (not sql), but I would like to sort on that column so it would be better if it were done in sql

Thanks a ton

Dan L

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-25 : 13:30:31 



;With CTE (ID,Name,NameChain)
AS
(SELECT ID,Name,Name
 FROM YourTable
WHERE ParentID IS NULL
UNION ALL
SELECT t.ID,t.Name,c.Name +','+t.Name
FROM YourTable t
INNER JOIN CTE c
ON c.ID=t.ParentID
)

SELECT * FROM CTE
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-25 : 13:31:24
Also see this

http://msdn.microsoft.com/en-us/library/ms186243.aspx
Go to Top of Page

dlouche
Starting Member

9 Posts
Posted - 2008-11-25 : 13:54:48
Thanks for the help. I have never heard of CTE before.

When I try your query I get the following error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "NameChain" of recursive query "CTE".


Here is your query as I have entered it:

;With CTE (ID,Name,NameChain)
AS
(SELECT ID,Name,Name
FROM Categories
WHERE ParentCategoryID IS NULL
UNION ALL
SELECT t.ID,t.Name,c.Name + ',' + t.Name
FROM Categories t
INNER JOIN CTE c
ON c.ID = t.ParentCategoryID
)

SELECT * FROM CTE

Thanks again!
Go to Top of Page

dlouche
Starting Member

9 Posts
Posted - 2008-11-25 : 14:05:00
It doesn't seem to like the use of 't.name' in the recursive part
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-11-25 : 14:09:26
Please show the structure of Categories.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

dlouche
Starting Member

9 Posts
Posted - 2008-11-25 : 14:16:54
I got it! SQL didn't like the type of the concat?? Here is what ended up working:

;With CTE (ID,Name,Name2)
AS
(SELECT ID,Name,Name
FROM Categories
WHERE ParentCategoryID IS NULL
UNION ALL
SELECT t.ID,t.Name,CONVERT(nvarchar(128), c.Name + ',' + t.Name)
FROM Categories t
INNER JOIN CTE c
ON c.ID = t.ParentCategoryID
)

SELECT * FROM CTE

Thanks again!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site