动态数据透视查询而不将查询存储为字符串 [英] Dynamic Pivot Query without storing query as String

问题描述

我对链接中执行动态数据透视查询的以下方法非常熟悉.是否有另一种方法可以执行动态数据透视，而无需将查询存储为字符串并在其中插入列字符串?

I am fully familiar with the following method in the link for performing a dynamic pivot query. Is there an alternative method to perform a dynamic pivot without storing the Query as a String and inserting a column string inside it?

http://www. simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

推荐答案

简短答案: 否

详细答案:

好吧，这仍然是否.但我会尽力解释原因.从今天开始，当您运行查询时，数据库引擎要求知道查询将返回的结果集结构(列数，列名，数据类型等).因此，当您从数据库中查询数据时，必须定义结果集的结构.考虑一下:您是否曾经运行过一个查询，而您事先不知道结果集的结构?

Well, that's still no. But I will try to explain why. As of today, when you run the query, the DB engine demands to be aware of the result set structure (number of columns, column names, data types, etc) that the query will return. Therefore, you have to define the structure of the result set when you ask data from DB. Think about it: have you ever ran a query where you would not know the result set structure beforehand?

即使您执行select *，这也同样适用，这只是一个糖语法.最后，返回的结构是此类表中的所有列".

That also applies even when you do select *, which is just a sugar syntax. At the end, the returning structure is "all columns in such table(s)".

通过组装字符串，可以动态生成所需的结构，然后再请求结果集.这就是为什么它起作用.

By assembling a string, you dynamically generate the structure that you desire, before asking for the result set. That's why it works.

最后，您应该意识到，动态地组装字符串可以从理论上并且潜在地(尽管不是很可能)为您提供具有无限列的结果集.当然，这是不可能的，并且会失败，但是我确定您了解其中的含义.

Finally, you should be aware that assembling the string dynamically can theoretically and potentially (although not probable) get you a result set with infinite columns. Of course, that's not possible and it will fail, but I'm sure you understood the implications.

更新

我发现了这一点，这加强了它不起作用的原因.

I found this, which reinforces the reasons why it does not work.

此处:

SSIS依赖于事先了解数据流的元数据以及 动态枢轴(这就是您所追求的)与 那个.

SSIS relies on knowing the metadata of the dataflow in advance and a dynamic pivot (which is what you are after) is not compatible with that.

我将继续在此处查找并添加.

I'll keep looking and adding here.

这篇关于动态数据透视查询而不将查询存储为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！