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 做到这一点而无需像这样的 hack?

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.

谢谢！
丹尼尔

推荐答案

您链接到的示例使用动态 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屋！