SQL Server : splitting the results of GROUP BY into a separate columns

Question

I've got a SQL Server 2008 R2 database with around 500 million rows of data in it, it currently looks like this

ID          Eventtype
201         1
201         3
201         4
201         1
201         1
664         1
664         0
664         1
664         3

I can't seem to find a query that will provide the data back in this format:

ID         Event0   Event1  Event2  Event3  Event4
201        0        3       0       1       1
664        1        2       0       1       0

This is as far as I've gotten at this point:

select distinct ID as ID, count(EventType)
from database.dbo.events 
group by questID, EventType

which spits data back to me like:

ID       EventType
201      0
201      3
201      0
201      1
201      1
664      1
664      2
664      0
etc.

This does display all the data I need, but the formatting and guesswork involved in trying to figure out which EventType is which is quite frustrating.

Can anyone suggest a better query that will return the data in a good format?

Answer 1

How about something like...

select ID, sum(Event0), sum(Event1), sum(Event2), sum(Event3), sum(Event4)
from (
    select ID, 
        case EventType when 0 then 1 else 0 end as Event0,
        case EventType when 1 then 1 else 0 end as Event1,
        case EventType when 2 then 1 else 0 end as Event2,
        case EventType when 3 then 1 else 0 end as Event3,
        case EventType when 4 then 1 else 0 end as Event4
    from dbo.events
) E
group by ID

• Assuming there are exactly 5 event types numbered 0 through 4.
• Depending how the table is indexed it may take a significant amount of sort space, and can fail if sufficient space is not available.

Answer 2

There is pivot functionality in Sql Server. If you have for instance 6 distinct events, you can use this:

select ID, [0], [1], [2], [3], [4], [5]
from events
pivot 
(
  -- aggregate function to apply on values
  count(EventType) 
  -- list of keys. If values of keys are not fixed,
  -- you will have to use dynamic sql generation 
  for EventType in ([0], [1], [2], [3], [4], [5])
) pvt

For dynamic pivot generation, see this SO post.

Btw, I believe that your original query should read:

select ID, EventType, count(EventType)
from events 
group by ID, EventType
order by ID, EventType

You can see it in action @ Sql Fiddle (scroll down to see pivoting results).