title: Sql server using variable in pivot query
body: <p>I have a returned string from a query that reads:</p>
  
  <pre><code> +----------------------+     
   |   returnquerystring  |
   +----------------------+
   |   exam1,exam2,exam3  |
   +----------------------+
  </code></pre>
  
  <p>I am using this returned string as column names in a pivot query.</p>
  
  <pre><code> select * from (select score,exam from table1) x
   pivot ( max(score) for exam in (exam1,exam2,exam3)
  </code></pre>
  
  <p>This query works giving me</p>
  
  <pre><code>+-------------+-----------+-----------+
  |    exam1    |   exam2   |   exam3   |
  +-------------+-----------+-----------+
  |      10     |     20    |     30    |
  +-------------+-----------+-----------+
  </code></pre>
  
  <p>However I have not been able to get the pivot "in" statement to use anything but the hard coded values of exam1,exam2,exam3.  For example I have used SSMS and created a query that successfully puts exam1,exam2,exam3 into @var1.  However @var1 will throws and error when used in place of exam1,exam2,exam3.</p>
  
  <pre><code> declare @var1 varchar(100)
   select @var1 = value from table 
   select * from (select score,exam from table1) x
   pivot ( max(score) for exam in (@var1)
  
   Incorrect syntax near '@var1'.
  </code></pre>
  
  <p>To verify that I was doing it correctly I did this and it worked.</p>
  
  <pre><code> declare @var1 int
   select top 1 @var1 = id from name 
   select * from name where id = @var1
  </code></pre>
  
  <p>This provided the data row for id 1 on the name table with no error.</p>
  
  <p>I have noticed in my experiments that (exam1,exam2,exam3) cannot be ('exam1,exam2,exam3') with the quotes.  </p>
  
  <p>I am using ColdFusion CFSCRIPT and it does appear that the single quotes are getting into the query so I tried various tests with ColdFusion functions to remove them with no success.  </p>
  
  <p>So I tried using the SQL Server function 'replace' around the @var1 and that throws an error about syntax at replace. </p>
  
  <p>This is when I tried using an example like above in SSMS and still got errors.  So by removing ColdFusion from the equation it still does not work.  My thought was to send the whole declare through pivot as a query to avoid ColdFusion issues but it does not work in SSMS.</p>
  
  <p>I am using SQL SERVER 8 and SSMS 11.</p>
  
  <p>Any ideas on how to make this work?</p>
  
  <pre><code> examColumns = exam1,exam2,exam3
  
  public any function qryExamScores(string examColumns) {
      thisQry = new Query();
      thisQry.setName("returnqry");
      thisQry.setDatasource(application.datasource);
   thisQry.addParam(name="columnNames",value=arguments.examColumns,cfsqltype="cf_sql_varchar");
      result = thisQry.execute(sql="
          select * from
          (select id,score,exam
          from table
          where value1 = 'XXXXX'
           and value2 = '11111') x
           pivot
           (
              max(score) for exam in (:columnNames)
           ) p
  
      ");
      returnqry = result.getResult();
      return returnqry;
  }
  </code></pre>