SQL Server Pivot是否可以不知道结果列名称? [英] Can SQL Server Pivot without knowing the resulting column names?

问题描述

我有一个看起来像这样的表:

I have a table that looks like this:

Month      Site          Val
2009-12    Microsoft      10
2009-11    Microsoft      12
2009-10    Microsoft      13
2009-12    Google         20
2009-11    Google         21
2009-10    Google         22

我想获得一个二维表，该表为我提供每个站点月份的"Val"，例如:

And I want to get a 2-dimension table that gives me the "Val" for each site's month, like:

Month      Microsoft      Google
2009-12        10           20
2009-11        12           21
2009-10        13           22

但是要注意的是，我不知道站点"中所有可能的值.如果出现一个新站点，我想在结果表中自动获得一个新列.

But the catch is, I don't know all the possible values that can be in "Site". If a new site appears, I want to automatically get a new column in my resulting table.

我看到的所有可以做到这一点的代码示例都要求我在查询文本中对"Microsoft and Google"进行硬编码.

All the code samples I saw that could do this required me to hardcode "Microsoft and Google" in the query text.
I saw one that didn't, but it was basically faking it by listing the Sites and generating a query on the fly (concatting a string) that had those column names in it.

是否没有一种方法可以使SQL Server 2008在没有这种黑客的情况下做到这一点?

Isn't there a way to get SQL Server 2008 to do this without a hack like that?

注意:我需要能够将其作为从ASP.Net发送的查询来运行，我不能执行存储过程或类似的事情.

NOTE: I need to be able to run this as a query that I send from ASP.Net, I can't do stored procedures or other stuff like that.

谢谢！
丹尼尔

Thanks!
Daniel

推荐答案

您链接的示例使用动态SQL.不幸的是，当事先不知道输出列时，没有其他内置方法可以在SQL Server中进行透视.

The example you linked to uses dynamic SQL. Unfortunately, there is no other built-in method for pivoting in SQL Server when the output columns are not known in advance.

如果数据不是太大，则可能最简单的是简单地从ASP.NET运行普通的行查询并在应用程序代码中执行数据透视.如果数据非常大，则必须在首先查询可能的列值之后动态生成SQL.

If the data is not too large, it's probably easiest to simply run a normal row query from ASP.NET and perform your pivot in the application code. If the data is very large, then you'll have to generate the SQL dynamically after first querying for the possible column values.

请注意，您实际上不需要编写生成动态SQL的SQL语句.您可以简单地在ASP.NET中生成SQL，这很可能会容易得多.只是不要忘记在生成的查询中插入它们之前，先将不同的Site值转义，并且不要忘记对SQL语句的任何部分进行参数化，而这些部分通常是不用透视表即可实现的.

Note that you don't actually need to write a SQL statement that generates dynamic SQL; you can simply generating the SQL in ASP.NET, and that will most likely be much easier. Just don't forget to escape the distinct Site values before chucking them in a generated query, and don't forget to parameterize whatever parts of the SQL statement that you normally would without the pivot.

这篇关于SQL Server Pivot是否可以不知道结果列名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！