SQL 带有分组依据的数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13853880/
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
Pivot Table with group by
提问by user87
select out.a,out.b from out
pivot (count([event]) for [date] in ([Mon],[Tues])) as event_count
group by out.a,out.b
while executing this query I am getting the following error:
在执行此查询时，我收到以下错误：
The multipart identifier out.a,out.b could not be bound
无法绑定多部分标识符 out.a,out.b
回答by Mahmoud Gamal
Remove the GROUP BYclause, and the alias outof the columns aand blike so:
取出GROUP BY子句和别名out列a和b像这样：
select a, b, [Mon], [Tues]
from out
pivot
(
count([event])
for [date] in ([Mon],[Tues])
) as event_count;
SQL Fiddle Demo
SQL 小提琴演示
Note that:When using the PIVOTtable operator, you do not need to explicitly specify the grouping elements, in your case a, bor in the source table, you need to remove it from the the query and from the source table. The PIVOToperator will automatically figures out the columns you want to GROUP BY, and these columns are those that were not specified in either the dateor the aggregation element the eventin your case, the rest will be the grouped columns.
注意：使用PIVOT表运算符时，不需要显式指定分组元素，在您的情况下a, b或在源表中，您需要将其从查询和源表中删除。该PIVOT操作会自动计算出你想要的列GROUP BY，而这些列是指那些没有在任何指定date或聚集元素event在你的情况，其余的将被分组列。
Thats why you have to list the columns' names explicitly instead of FROM out, incase the table outcontains columns other than a, b, date, event. In this case, you have to do this:
这就是为什么您必须明确列出列的名称而不是FROM out，以防表out包含除a, b, date, event. 在这种情况下，你必须这样做：
select a, b, [Mon], [Tues]
from
(
SELECT a, b, "date", "event"
FROM out
) t
pivot
(
count([event])
for [date] in ([Mon],[Tues])
) as event_count;
回答by Aleksandr Fedorenko
sounds like you need replace out.a on [Mon] and out.b on [Tues]
听起来你需要在 [Mon] 替换 out.a，在 [Tues] 替换 out.b
SELECT [Mon], [Tues]
FROM out
PIVOT (
COUNT([event]) FOR [date] in ([Mon],[Tues])
) AS event_count
Demo on SQLFiddle
回答by praveen
I'm not sure about your query but may be this is what your looking for
我不确定您的查询，但可能这就是您要找的
Select a,b, [Mon],[Tues] from
(
Select a,b,date,event from out
group by a,b,date,event
)L
PIVOT
(
count(event) for [date] in ([Mon],[Tues])
) event_count
