Sql server在pivot查询中使用变量

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.

但是，除了exam1，exam2，exam3的硬编码值之外，我还没有能够使用“in”语句来使用任何东西。例如，我使用了SSMS并创建了一个成功将exam1，exam2，exam3放入@ var1的查询。但是当用于代替exam1，exam2，exam3时，@ var1将抛出并出错。

 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.

这为名称表上的id 1提供了数据行，没有错误。

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

我在实验中注意到（考试1，考试2，考试3）不能用引号括起来（'exam1，exam2，exam3'）。

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.

我正在使用ColdFusion CFSCRIPT，看起来单引号进入查询，所以我尝试使用ColdFusion函数进行各种测试以删除它们但没有成功。

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

所以我尝试在@ var1周围使用SQL Server函数'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.

这是我在SSMS中尝试使用上述示例时仍然出现错误的原因。因此，通过从等式中删除ColdFusion，它仍然不起作用。我的想法是通过pivot发送整个声明作为查询以避免ColdFusion问题，但它在SSMS中不起作用。

I am using SQL SERVER 8 and SSMS 11.

我正在使用SQL SERVER 8和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;
}

2 个解决方案

#1

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

您需要使用动态SQL在数据透视表中使用变量（@ var1）的值

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

#2

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

将exam1，exam2，exam3作为param varchar传递给：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.

Queryparam（或绑定变量）只能用于文字。由于“exam1，exam2，exam3”在此特定查询中用作列名，因此您无法对其应用queryparam。当您这样做时，您告诉数据库这些值是简单的字符串。这会导致错误，因为pivot需要对象名称，而不是字符串。

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.

删除queryparam，查询将按预期工作。但是，显然可能会将您的数据库暴露给sql注入（取决于columnNames的来源）。这同样适用于使用任何动态SQL（exec，sp_executesql，...）。因此，在实施此方法之前，请务必完全验证输入。

...
// 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]";

#1