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?

Which version of Sql Server is this? – Nikola Markovinović Jun 12 '12 at 23:59 — Nikola Markovinović, Jun 12 '12 at 23:59

score 4 · Accepted Answer · answered Jun 13 '12 at 00:03

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.

Awesome, thanks! This worked perfectly. Nikola's reply was also correct, but the pivot table took 14min to run where joshp's case version took 6min. — user1452458, Jun 13 '12 at 00:36

score 4 · Answer 2 · edited May 23 '17 at 12:12

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

Thanks! This did work just fine, it took a bit longer to run than joshp's case version. — user1452458, Jun 13 '12 at 00:37

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

2 Answers2

Linked