title: How do I convert rows to columns that resulted from a CTE?
body: <p>I have a CTE expression of the following form:</p>
  
  <pre><code>WITH nCTE AS
  (
     SELECT *
     FROM 
     TableA X INNER JOIN TableB Y
     ON X.ID = Y.ID
  )
  SELECT *
  FROM
  (
     SELECT ...
     FROM nCTE
  )
  WHERE ...
  GROUP BY ...
  ORDER BY ...
  </code></pre>
  
  <p>The results of this query look something like this:</p>
  
  <pre><code>ID   Name   Badge   Type   Value
  1   John    Blue    Pre    10
  1   John    Blue    Post   20
  1   John    Blue    Cur    30
  1   John    Green   Pre    50
  1   John    Green   Post   30
  1   John    Green   Cur    20
  </code></pre>
  
  <p>I am trying to get the results in the following form:</p>
  
  <pre><code>ID   Name   Badge   Pre   Cur   Post
  1    John   Blue    10    20    30
  2    John   Green   50    30    20
  </code></pre>
  
  <p>As I am using a CTE, I am not sure if I can do this in a direct way or I should self-join the result multiple times to get this (this will just make everything look long, ugly and complex). Is there a good way to achieve this?</p>
  
  <p><strong>UPDATE: Trying this returns a NULL value</strong></p>
  
  <pre><code>WITH nCTE AS
  (
     SELECT *
     FROM 
     TableA X INNER JOIN TableB Y
     ON X.ID = Y.ID
  )
  SELECT ID, Name, Badge, [0] AS 'Pre', [1] AS 'Cur', [2] AS 'Post'
  FROM
  (
      SELECT *
      FROM
      (
         SELECT ...
         FROM nCTE
      )
      WHERE ...
      GROUP BY ...
  ) Y
  PIVOT
  (
     MAX(Value)
     FOR Type IN ([0], [1], [2])
  ) AS PivotTable
  ORDER BY ...
  </code></pre>
  
  <p>While the query itself works, it returns the following:</p>
  
  <pre><code>ID   Name   Badge   Pre     Cur     Post
  1    John   Blue    NULL    NULL    NULL
  2    John   Green   NULL    NULL    NULL
  </code></pre>
  
  <p>Any suggestions as to why this is happening?</p>