CTE SQL Server 2

WITH TestCTE (ID
,Name
,ParentId
,Level)
AS
(
SELECT ID
,Name
,ParentId
,0 Level
FROM dbo.MyFirends F1P
UNION ALL
SELECT F.ID
,F.Name
,F.ParentId
,Level - 1
FROM dbo.MyFirends F
INNER JOIN TestCTE CTE ON F.ID = CTE.ParentId
)

select ID
,Name
,ParentId
,-1*Level
from TestCTE C

CTE SQL Server

http://msdn.microsoft.com/en-us/library/ms190766.aspx


CREATE TABLE [dbo].[MyFirends](
[ID] [int] NULL,
[Name] [nchar](10) NULL,
[Date] [date] NULL,
[Address] [nchar](100) NULL,
[ParentId] [int] NOT NULL
) ON [PRIMARY]

-------------------------------------------------------------


WITH TestCTE (ID
,Name
,ParentId
,Level)
AS
(
SELECT ID
,Name
,ParentId
,0 Level
FROM dbo.MyFirends F1P
WHERE ParentId = 0
UNION ALL
SELECT F.ID
,F.Name
,F.ParentId
,Level + 1
FROM dbo.MyFirends F
INNER JOIN TestCTE CTE ON F.ParentId = CTE.ID
)

select ID
,Name
,ParentId
,Level
from TestCTE C