Get direct hierarchy in SQL without siblings of searched id

I have a pretty simple table called Types which contians ID, ParentID and Name. What I need is to get the full hierarchy from an ID, but without including the siblings of the searched ID. I have written the following SQL,

;WITH cte AS ( 
   SELECT ID, ParentID, [Name]
   FROM   [Types]
   WHERE  ID=246
   UNION ALL 
   SELECT t1.ID, t1.ParentID, t1.[Name]
   FROM   [Types] t1 
   INNER JOIN cte ON cte.ID=t1.ParentID 
    ) 
SELECT * FROM cte 

which gives me the full hierarchy of the table (with the topmost parent being id ‘246’):

So far, so good. What I would like now to get from the query is the full hierarchy of ID: 1384, but without the siblings of ID 1384 (and the siblings’ children, grandchildren etc.). So it should return the following result:

+------+----------+------------------+
| ID   | ParentID | Name             |
+------+----------+------------------+
| 246  | 2        | Approvals        |
+------+----------+------------------+
| 1384 | 246      | ProductStatus    |
+------+----------+------------------+
| 1517 | 1384     | NewStatus        |
+------+----------+------------------+
| 1520 | 1384     | NewSiblingStatus |
+------+----------+------------------+
| 1519 | 1517     | NewNewStatus     |
+------+----------+------------------+

I’ve read various articles on retrieving hierarchies, but so far haven’t come any closer to solving this problem or found someone with a similar problem.

Answer

One approach is to use two recursive common table expressions. One to get the parents, the other to get the children. You can then union them to get the result that you want – union (rather than union all) is on purpose to eliminate the duplicate on the initial node.

WITH 
    cte_children AS ( 
       SELECT ID, ParentID, [Name], 0 lvl
       FROM [Types]
       WHERE ID = 1384
       UNION ALL 
       SELECT t.ID, t.ParentID, t.[Name], lvl - 1
       FROM [Types] t
       INNER JOIN cte_children c ON c.ID = t.ParentID 
    ) 
    cte_parents AS ( 
       SELECT ID, ParentID, [Name], 0 lvl
       FROM [Types]
       WHERE  ID = 1384
       UNION ALL 
       SELECT t.ID, t.ParentID, t.[Name], lvl + 1
       FROM [Types] t
       INNER JOIN cte ON cte.ParentID = t.ID 
    ) 
SELECT * FROM cte_parents
UNION
SELECT * FROM cte_children  
ORDER BY lvl

Note that I added a column called lvl to the query, that computes the depth of each node (the intiial note has 0, parents have negative lvls, and children have positive lvls) – this is handy to sort the final resultset.