Pivot Table with group by

Question

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

Answer 1

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 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.

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;