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

sql-server, sql-server-2008-r2

Solution

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.

Problem

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?

Original source

Related problems