SQL Server:将 GROUP BY 的结果拆分为单独的列 [英] SQL Server : splitting the results of GROUP BY into a separate columns

问题描述

我有一个包含大约 5 亿行数据的 SQL Server 2008 R2 数据库，目前看起来像这样

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

这是我目前得到的:

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.

这确实显示了我需要的所有数据，但是试图找出哪个 EventType 所涉及的格式和猜测非常令人沮丧.

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?

推荐答案

怎么样...

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

• 假设正好有 5 种事件类型，编号为 0 到 4.
• 根据表的索引方式，它可能会占用大量的排序空间，如果没有足够的空间可用，则可能会失败.

这篇关于SQL Server:将 GROUP BY 的结果拆分为单独的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！