Return hierarchy without creating any views or tables?

I have a table with columns and data as follows:

Table1
ID  Name    PID
A1  Apple   P1
B1  Book    A1
B2  开发者_StackOverflow中文版Brook   A1
C1  Cat     B1
C2  Cook    B1
C3  Car     B1
D1  Dog     B2
D2  Doll    B2
E1  Egg     C1

I want the results as follows:

ID  Name                    Depth
B1  Apple\Book              2
C1  Apple\Book\Cat          3
E1  Apple\Book\Cat\Egg      4
C2  Apple\Book\Cook         3
C3  Apple\Book\Car          3
B2  Apple\Brook             2
D1  Apple\Brook\Dog         3
D2  Apple\Brook\Doll        3

The relationship is that a row is a child of another row if PID of that row is equal to ID of the parent row.

Apple is the base. So the first statement would be something like:

Select ID, Name, 2 from Table1 where PID=(select ID from Table1 where Name='Apple');

My current solution is creating a lot of views and save all those similar statements like that to views and union them together. But I don't want that. I want to finish that within 1 select statement.A

I don't know if there's an elegant way to get the exact ordering you listed, but here is one approach with a recursive CTE:

;WITH cte AS
(
    SELECT ID, Name, Depth = 1
        FROM dbo.Table1 
        WHERE Name = 'Apple'
    UNION ALL
    SELECT t.ID, t.Name, Depth = cte.Depth + 1
        FROM cte 
        INNER JOIN dbo.Table1 AS t
        ON t.PID = cte.ID
)
SELECT ID, Name, Depth
FROM cte
WHERE Depth > 1;

declare @Table1 table
(
  ID varchar(2),
  Name varchar(10),
  PID varchar(2)
)

insert into @Table1 values  
('A1',  'Apple',   'P1'),
('B1',  'Book',    'A1'),
('B2',  'Brook',   'A1'),
('C1',  'Cat',     'B1'),
('C2',  'Cook',    'B1'),
('C3',  'Car',     'B1'),
('D1',  'Dog',     'B2'),
('D2',  'Doll',    'B2'),
('E1',  'Egg',     'C1')

;with C as
(
  select T.ID,
         cast(T.Name as varchar(max)) as Name,
         1 as Depth
  from @Table1 as T
  where T.Name = 'Apple'
  union all
  select T.ID,
         cast(C.Name+'\'+T.Name as varchar(max)),
         C.Depth + 1
  from @Table1 as T
    inner join C
      on T.PID = C.ID
)
select C.ID,
       C.Name,
       C.Depth
from C
where C.Depth > 1
order by C.Name

Edit With out the Apple.

;with C as
(
  select T.ID,
         cast(T.Name as varchar(max)) as Name,
         1 as Depth
  from @Table1 as T
    inner join @Table1 as TP
      on T.PID = TP.ID
  where TP.Name = 'Apple'
  union all
  select T.ID,
         cast(C.Name+'\'+T.Name as varchar(max)),
         C.Depth + 1
  from @Table1 as T
    inner join C
      on T.PID = C.ID
)
select C.ID,
       C.Name,
       C.Depth
from C
order by C.Name

Here is the query I came up with on PostgreSQL. I don't have SQL Server, so I can't test, but Google has led me to believe that this query structure will work on SQL Server as well.

SELECT t1.*, (
    WITH q AS (
        SELECT t2.*
        FROM Table1 AS t2
        WHERE t2.ID = t1.ID
           OR (t1.ID IS NULL AND t2.ID IS NULL)

        UNION ALL

        SELECT t3.*
        FROM Table1 AS t3
        JOIN q
        ON t3.ID = q.PID
        AND t3.ID <> 'P1'
        -- 'P1' on the above line is the "sentinel" value you want to
        -- stop traversing at.  Remove the AND clause altogether if you
        -- want to traverse up to the ultimate root record.
    )
    SELECT COUNT(q.ID)
    FROM q
) AS depth
FROM Table1 AS t1;

Here is an example run on PostgreSQL. Note that PostgreSQL requires WITH RECURSIVE when declaring a common table expression that references itself. This, and the sample data, are the only differences between the two queries:

$ WITH Table1(ID, Name, PID) AS (VALUES
$     ('A1', 'Apple', 'P1'),
$     ('B1', 'Book',  'A1'),
$     ('B2', 'Brook', 'A1'),
$     ('C1', 'Cat',   'B1'),
$     ('C2', 'Cook',  'B1'),
$     ('C3', 'Car',   'B1'),
$     ('D1', 'Dog',   'B2'),
$     ('D2', 'Doll',  'B2'),
$     ('E1', 'Egg',   'C1')
$ )
$ SELECT t1.*, (
$     WITH RECURSIVE q AS (
$         SELECT t2.*
$         FROM Table1 AS t2
$         WHERE t2.ID = t1.ID
$            OR (t1.ID IS NULL AND t2.ID IS NULL)
$
$         UNION ALL
$
$         SELECT t3.*
$         FROM Table1 AS t3
$         JOIN q
$         ON t3.ID = q.PID
$         AND t3.ID <> 'P1'
$     )
$     SELECT COUNT(q.ID)
$     FROM q
$ ) AS depth
$ FROM Table1 AS t1;
 id | name  | pid | depth
----+-------+-----+-------
 A1 | Apple | P1  |     1
 B1 | Book  | A1  |     2
 B2 | Brook | A1  |     2
 C1 | Cat   | B1  |     3
 C2 | Cook  | B1  |     3
 C3 | Car   | B1  |     3
 D1 | Dog   | B2  |     3
 D2 | Doll  | B2  |     3
 E1 | Egg   | C1  |     4
(9 rows)