分组依据的数据透视表 [英] Pivot Table with group by

问题描述

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:

无法绑定多部分标识符out.a，out.b

The multipart identifier out.a,out.b could not be bound

推荐答案

删除GROUP BY子句，并删除列a和b的别名out，如下所示:

Remove the GROUP BY clause, and the alias out of the columns a and b like so:

select a, b, [Mon], [Tues] 
from out
pivot 
(
  count([event]) 
  for [date] in ([Mon],[Tues])
) as event_count;

SQL小提琴演示

请注意::使用PIVOT表运算符时，无需显式指定分组元素，在您的情况下a, b或在源表中，您需要将其删除从查询和从源表. PIVOT运算符将自动找出要GROUP BY的列，这些列是在您的情况下在date或event的聚合元素中未指定的那些列，其余的将是分组的列.

SQL Fiddle Demo

Note that: When using the PIVOT table operator, you do not need to explicitly specify the grouping elements, in your case a, b or in the source table, you need to remove it from the the query and from the source table. The PIVOT operator will automatically figures out the columns you want to GROUP BY, and these columns are those that were not specified in either the date or the aggregation element the event in your case, the rest will be the grouped columns.

这就是为什么如果表out包含a, b, date, event以外的列，则必须显式列出列名称而不是FROM out的原因.在这种情况下，您必须这样做:

Thats why you have to list the columns' names explicitly instead of FROM out, incase the table out contains columns other than a, b, date, event. In this case, you have to do this:

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;

这篇关于分组依据的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！