sql-server - Sql server 在数据透视查询中使用变量

Question

我有一个查询返回的字符串，内容为:

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

我在数据透视查询中使用这个返回的字符串作为列名。

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

这个查询工作给我

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

但是，除了exam1、exam2、exam3 的硬编码值之外，我无法使用pivot“in”语句来使用任何东西。例如，我使用了 SSMS 并创建了一个成功地将考试 1、考试 2、考试 3 放入 @var1 的查询。但是，@var1 在代替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'.

为了验证我做的是否正确，我做了这个并且它起作用了。

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

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

我在实验中注意到 (exam1,exam2,exam3) 不能是 ('exam1,exam2,exam3') 带引号。

我正在使用 ColdFusion CFSCRIPT 并且单引号似乎确实进入了查询，因此我尝试使用 ColdFusion 函数进行各种测试以将其删除，但没有成功。

因此，我尝试在 @var1 周围使用 SQL Server 函数“replace”，这在替换时引发了有关语法的错误。

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

我正在使用 SQL SERVER 8 和 SSMS 11。

关于如何使这项工作的任何想法？

 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

您需要使用 Dynamic SQL在 Pivot 中使用变量(@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

如果您想在数据透视列中拥有多个然后一个值，请使用它。

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