title: PIVOT on Common Table Expression
body: <p>I have a CTE as follows</p>
  
  <pre><code>WITH  details
          AS ( SELECT FldId
                     ,Rev
                     ,Words
                     ,row_number() OVER ( PARTITION BY FldId ORDER BY Rev DESC ) AS rn
               FROM   WorkItemLongTexts
               WHERE  ID = 2855
             )
    SELECT  f.ReferenceName
           ,d.FldId
           ,Rev
           ,Words
    FROM    details AS d
            INNER JOIN Fields AS f ON f.FldId = d.FldId
    WHERE   d.rn = 1 ;
  </code></pre>
  
  <p>The above returns the following output</p>
  
  <pre><code>ReferenceName    |   FldId      |    Rev     |    Words
  Description            52            2            Description here  
  Objectives           10257           2            Objectives here  
  Specification        10258           6            Specification here  
  Requirements          10259           6            Requirements here  
  </code></pre>
  
  <p>I want to apply PIVOT (or whatever is the best option) so that i can get output as follows</p>
  
  <pre><code>Description         |     Objectives     |   Specification      |  Requirements  
  </code></pre>
  
  <p>Description here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Objectives here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;      Specification here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;     Requirements here</p>
  
  <p>Pls. suggest.</p>
  
  <p>Thanks</p>