SQL 行值作为列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3231312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL row value as column name
提问by Zind
I'm pretty experienced in C#, but still mostly a beginner in SQL.
We have an application in C#, using an MSSQL database.
One part of our application is simply a list of pre-written queries as reports that the application shows by simply running the query and sticking the returned table into a gridView.
The user is requesting a new report that I'm not entirely sure is even possible with just SQL and is going to require manipulation of the returned data to get it to look right.
我在 C# 方面很有经验，但仍然主要是 SQL 的初学者。
我们有一个 C# 应用程序，使用 MSSQL 数据库。
我们的应用程序的一部分只是一个预先编写的查询列表作为报告，应用程序通过简单地运行查询并将返回的表粘贴到 gridView 中来显示这些查询。用户正在请求一份新报告，我不完全确定仅使用 SQL 是否可行，并且需要对返回的数据进行操作以使其看起来正确。
The data that the users want, in the way they want it presented would require me to be able to take this table:
用户想要的数据，以他们想要的方式呈现，需要我能够获取这张表：
Date Category Count --------------------- date1 Cat1 x1 date1 Cat2 y1 ... date1 CatN z1
gotten from this query:
从这个查询中得到：
select Date, Category, COUNT(*) as 'Count'
from Table
group by Date, CategoryDate Cat1 Cat2 ... CatN --------------------------- date1 x1 y1 ... z1 date2 x2 y2 ... z2
so that I can join it by date to the other half of the data they want.
这样我就可以按日期将其加入他们想要的另一半数据。
Long-winded explanation, short question and followup: is this possible to do, no matter how painfully, with just SQL?
If so, how?
冗长的解释，简短的问题和跟进：这是否可以做到，无论多么痛苦，只用 SQL？
如果是这样，如何？
采纳答案by Donnie
You need to use pivot. One issue that may give you problems is that you must know how many categories you have since a query can't ever return a dynamic number of columns (not even one that you're pivoting).
您需要使用pivot. 可能会给您带来问题的一个问题是您必须知道您有多少个类别，因为查询永远无法返回动态数量的列（甚至不是您正在旋转的列）。
I don't have your schema, but this should be about right (may take a few touchups here and there, but it should get you started)...
我没有你的架构，但这应该是正确的（可能需要在这里和那里进行一些修饰，但它应该让你开始）......
select
Date, [1] as Cat1, [2] as Cat2, [3] as Cat3, ...
from
(select date, category from table) p
pivot (
count(*)
for
Category in ([1],[2],[3],...)
) pivoted
order by
pivoted.date
