Sql server using variable in pivot request

Question

I have a return string from a query that reads:

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

I use this returned row as the column names in the pivot query.

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

This request works by giving me

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

However, I was not able to force the operator "in my own" to use anything but the hard-coded values ​​of exam1, exam2, exam3. For example, I used SSMS and created a query that successfully places exam1, exam2, exam3 at @ var1. However, @ var1 will cause errors and errors when used instead of exam 1, 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 make sure that I am doing it right, I did it and it worked.

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

This provided a data row for identifier 1 in the name table without errors.

In my experiments, I noticed that (exam1, exam2, exam3) cannot be ("exam1, exam2, exam3") with quotes.

I use ColdFusion CFSCRIPT and it seems that single quotes fall into the query, so I tried various tests with ColdFusion functions to remove them without success.

So, I tried to use the SQL Server replace function around @ var1 and it throws a syntax error when replacing.

This is when I tried to use the example described above in SSMS and still got errors. Therefore, removing ColdFusion from the equation, it still does not work. My thought was to send the whole ad via pivot as a request to avoid ColdFusion problems, 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 variable value (@ 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 than one value in columnar 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 varchar parameter as: parametervalue

Queryparam (or bind variables) can only be used for literals. Since "exam1, exam2, exam3" are used as the column names in this particular query, you cannot apply queryparam to them. When you do this, you tell the database that these values ​​are simple strings. This causes an error because pivot expects object names, not strings.

Remove the queryparam and the query will work as expected. However, it is obvious what your database can provide for SQL injection (depending on the source columnNames ). The same applies to the use of any dynamic SQL ( exec , sp_executesql , ...). Therefore, be sure to fully verify the input data 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:

In addition, you should probably bracket the column names to avoid syntax errors if the values ​​contain spaces or other invalid characters for the column names.

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