title: How to recursively expand rows in sql server?
body: <p>In MS SQL Server, if I have 2 tables like so</p>
  <pre><code>Group
  ------------
  ID GroupName
  1  A
  2  B
  3  C
  
  GroupMembership
  ---------------
  Value GroupID  Type
  1     2        Node
  3     2        Node
  2     1        Group
  4     1        Node
  5     3        Node
  </code></pre>
  <p>In the membership table, if the type is Node, then it is just a normal value. But if its type is Group, then it is referring to the group defined in the Group table where the Value is the GroupID.</p>
  <p>So the above querying for GroupID=1, would result in this kind of tree</p>
  <pre><code>4, (1, 3)
  </code></pre>
  <p>But in theory I could make any kind of tree with any amount of levels of nested brackets, by correctly setting up the group references between both tables.</p>
  <p>The question is, how can I do a select statement for GroupID=1, such that it would recursively expand all the related records (even deeply nested ones) and return the membership records?</p>
  <p>So ideally it would return</p>
  <pre><code>1     2        Node
  3     2        Node
  2     1        Group
  4     1        Node
  </code></pre>
  <p>My current query only returns this as it doesn't know how to expand the rows recursively.</p>
  <pre><code>2     1        Group
  4     1        Node
  </code></pre>
  <p>What is a simple sql query to do this?</p>