title: Using CTE with a dynamic pivot
body: <p>I'm trying to use <a href="https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query">This question</a> to perform a dynamic pivot, but I want to use a CTE to get the initial data. </p>
  
  <p>My query looks like this: </p>
  
  <pre><code>DECLARE  @cols  AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX);
  
  WITH dataSet (coDate, TransactionDate, TotalBalance, TransDate, collected)
  AS 
  (   *SELECT STATEMENT ) 
  
  
  SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
      FROM dataSet c
      FOR XML PATH(''), TYPE
      ).value('.', 'NVARCHAR(MAX)') 
  ,1,1,'')
  
  
   set @query = 'SELECT coDate, ' + @cols + ' from 
          (
              select coDate
                  , TotalBalance
                  , collected
                  , TransDate
              from dataSet
         ) x
          pivot 
          (
               SUM(collected)
              for category in (' + @cols + ')
          ) p '
  
  
  execute(@query)
  </code></pre>
  
  <p>And the error SQL gives me is Incorrect syntax near the keyword 'SET'. I did try adding a semicolon and go as well as a comma before the SET statement, but this the first time I've used PIVOT so I'm not sure how CTE interacts with it. </p>