title: T-SQL :Parent-Child Hierarchy - based on @Variable
body: <pre><code>DECLARE @Categories TABLE (
          CategoryID INT, 
          CategoryName VARCHAR(20),
          ParentID INT )
  
  
          INSERT INTO @Categories(CategoryID, CategoryName, ParentID)
              SELECT 1, 'Company', NULL UNION ALL
              SELECT 2, 'Dept A', 1 UNION ALL
              SELECT 3, 'Dept B', 1 UNION ALL
              SELECT 4, 'DA_Sub_1', 2 UNION ALL
              SELECT 5, 'DA_Sub_2', 2 UNION ALL
              SELECT 6, 'DA_Sub_3', 2 UNION ALL
              SELECT 7, 'DA_Sub_4', 2 UNION ALL
              SELECT 8, 'DA1_I', 4 UNION ALL
              SELECT 9, 'DA1_II', 4
          DECLARE @ID int = 4
          DECLARE @Matched int = (SELECT CASE WHEN ParentID = 0 THEN CategoryID ELSE ParentID END FROM @Categories WHERE CategoryID = @ID)
  
          ;WITH cte AS (
              SELECT CategoryID, CategoryName, ParentID
              FROM @Categories WHERE ParentID IS NULL
              UNION ALL
              SELECT c.CategoryID, c.CategoryName, c.ParentID
              FROM @Categories c
              INNER JOIN cte p ON p.CategoryID = c.ParentID
  
      )
  </code></pre>
  
  <p>Output </p>
  
  <pre><code>CID CName           PID
  1   Company         NULL
  2   Dept A          1
  3   Dept B          1
  4   DA_Sub_1        2
  5   DA_Sub_2        2
  6   DA_Sub_3        2
  7   DA_Sub_4        2
  8   DA1_I           4
  9   DA1_II          4
  </code></pre>
  
  <p>I want to make it so that, I can pass a variable and it would </p>
  
  <pre><code>1) list Self + Parents
  2) list Self + Siblings
  3) list Self + Children
  4) list Parents
  5) list Siblings
  6) list children
  7) list Parents + Self + Siblings.
  8) list self + siblings + children
  9) list Parents + Self + Siblings + Children (if any).
  </code></pre>
  
  <p>How can I achieve these things? (this is for my own knowledge as I am trying to learn how hierarchy work in t-sql).</p>
  
  <p>Thank you</p>