Sql server using variable in pivot query

Question

I have a returned string from a query that reads:

 +----------------------+     
 |   returnquerystring  |
 +----------------------+
 |   exam1,exam2,exam3  |
 +----------------------+

I am using this returned string as column names in a pivot query.

 select * from (select score,exam from table1) x
 pivot ( max(score) for exam in (exam1,exam2,exam3)

This query works giving me

+-------------+-----------+-----------+
|    exam1    |   exam2   |   exam3   |
+-------------+-----------+-----------+
|      10     |     20    |     30    |
+-------------+-----------+-----------+

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.

 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'.

To verify that I was doing it correctly I did this and it worked.

 declare @var1 int
 select top 1 @var1 = id from name 
 select * from name where id = @var1

This provided the data row for id 1 on the name table with no error.

I have noticed in my experiments that (exam1,exam2,exam3) cannot be ('exam1,exam2,exam3') with the quotes.

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.

So I tried using the SQL Server function 'replace' around the @var1 and that throws an error about syntax at replace.

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.

I am using SQL SERVER 8 and SSMS 11.

Any ideas on how to make this work?

 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;
}

Answer 1

You need to use Dynamic SQL to use the value of variable(@var1) inside Pivot

declare @var1 varchar(100)='',@sql nvarchar(max)
 select top 1 @var1 = value from table 

 set @sql = 'select * from (select score,exam from table1) x
 pivot ( max(score) for exam in (['+@var1+'])) piv'

exec sp_executesql @sql

If you want to have more then one value in pivot columns use this.

SELECT @var1 += '[' + Isnull(CONVERT(VARCHAR(50), value), '') + '],'
FROM   table

SELECT @var1 = LEFT(@var1, Len(@var) - 1)

SET @sql = 'select * from (select score,exam from table1) x
            pivot ( max(score) for exam in (' + @var1 + ')) piv'

EXEC Sp_executesql @sql 

Answer 2

passing exam1,exam2,exam3 as a param varchar as :parametervalue

Queryparam (or bind variables) can only be used on literals. Since "exam1,exam2,exam3" are being used as column names in this specific query, you cannot apply queryparam to them. When you do that, you are telling the database those values are simple strings. That causes an error because pivot expects object names, not strings.

Remove the queryparam and the query will work as expected. However, obviously that may expose your database to sql injection (depending on the source of columnNames). The same applies to using any dynamic SQL (exec, sp_executesql, ...). So be sure to fully validate the input before implementing this approach.

...
// build pivot statement with dynamic column names
columnNames = "exam1,exam2,exam3";
sqlString = "SELECT * 
             FROM  (
                     SELECT score,exam 
                     FROM   table1
                  ) x 
             PIVOT
             ( 
                MAX(score) FOR exam IN ("& columnNames &") 
             ) 
             AS pvt ";
result = qry.execute( sql=sqlString ).getResult(); 
writeDump( result );

Edit:

Also, you should probably enclose the column names in brackets to avoid syntax errors if the values contain spaces, or other invalid characters for column names.

 "[exam1],[exam2],[exam3]";