CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
user87
user87

Reputation: 81

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:

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

Upvotes: 7

Views: 37548

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

sounds like you need replace out.a on [Mon] and out.b on [Tues]

SELECT [Mon], [Tues]
FROM out
PIVOT (
       COUNT([event]) FOR [date] in ([Mon],[Tues])
       ) AS event_count

Demo on SQLFiddle

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

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;

Upvotes: 8

praveen
praveen

Reputation: 12271

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

Upvotes: 1

Related Questions