更新时间:2023-01-16 17:36:19
虽然你可以使用 EXECUTE [ ^ ]运行动态SQL语句,我是如果这是一个好方法真的很可疑。
如果你是动态的话定义结果集列不会因为你不知道列而难以从光标中选择值吗?
当你有条件变化时,动态sql是可行的,但我会考虑其他方法。
- 编辑
添加了一个小例子:使用UNPIVOT计算动态列的聚合 [ ^ ]
While you can use EXECUTE[^] to run a dynamic SQL statement, I''m really suspicious if this is a good approach.
If you dynamically define the result set columns wouldn''t this result into a situation where you have difficulties in selecting the values from the cursor since you don''t know the columns?
Dynamic sql could be feasible when you have changing conditions, but I would consider other kind of approaches for this.
-- EDIT
Added a small example: Calculate aggregates for dynamic columns using UNPIVOT[^]
将查询构建为字符串并将其作为动态sql语句执行。请参阅此文章作为一系列可能性:在存储中构建动态SQL程序 [ ^ ]
Build the query as string and execute it as dynamic sql statement. See this article as a good collection of possibilities: Building Dynamic SQL In a Stored Procedure[^]
这里 [ ^ ]您有一个解决方案,可以使用xml支持将表行转换为名称 - 值对。您可以使用相同的方法仅过滤掉列表中的字段,并按照您的提及汇总其值。
Here[^] you have a solution to transform a table row to name-value pairs using the xml support. You can use the same approach to filter out only the fields that are in the list, and summarize their values as you mentioned.