title: Windowed function/pivot way to aggregate different values of the same column into different columns?
body: <p>I have a hierarchy of tables:</p>
  
  <ul>
  <li><code>GrandParentFoo</code>, which has zero or more </li>
  <li><code>ParentFoo</code>, which has zero or more</li>
  <li><code>ChildFoo</code></li>
  </ul>
  
  <p><code>ParentFoo</code> and <code>ChildFoo</code> both have a <code>Status</code> column with a total of 4 possible statuses:</p>
  
  <ul>
  <li>Pending (1)</li>
  <li>Active (2)</li>
  <li>Paused (3)</li>
  <li>Complete (4)</li>
  </ul>
  
  <p>I am trying to write a query that gives me a rollup for any particular <code>GrandParentFoo</code> along the following lines:</p>
  
  <ul>
  <li>GrandParentFooId</li>
  <li>Total ParentFoos</li>
  <li>Total ParentFoos Pending</li>
  <li>Total ParentFoos Active</li>
  <li>Total ParentFoos Paused</li>
  <li>Total ParentFoos Completed</li>
  <li>Total ChildFoos</li>
  <li>Total ChildFoos Pending</li>
  <li>Total ChildFoos Active</li>
  <li>Total ChildFoos Paused</li>
  <li>Total ChildFoos Completed</li>
  </ul>
  
  <p>I was starting down the path of:</p>
  
  <pre><code>select
      gp.GrandParentFooId,
      count(distinct pf.ParentFooId) as TotalParentFoos,
      sum(case pf.Status 
          when 1 then 1
          else 0 end) as TotalParentFoosPending
  </code></pre>
  
  <p>...when I realized this was going to give me an inflated count where multiple <code>ChildFoo</code> records existed on the <code>ParentFoo</code> records.</p>
  
  <p>Do I have to write this out as a series of CTE's, or is there a cleaner, simpler way to do this?  It seems like a pivot or windowed function of some kind would work here, but I can't conceptualize it.</p>