title: PIVOT on hierarchical data
body: <p>I have a table like this:</p>
  
  <pre>
  Id  Name                parentId
  --------------------------------
  5   Rollers             2
  2   Paint and Brushes   1
  1   Decorating          NULL
  </pre>
  
  <p>Using PIVOT or any other single query, can I get an output like this:</p>
  
  <pre>
  cat1id   cat1name      cat2id    cat2name             cat3id    cat3Name
  ------------------------------------------------------------------------
  1        Decorating    2         Paint and Brushes    5         Rollers
  </pre>