分别计算一列中的值 [英] Counting values in a column separately

问题描述

我的数据库中有一个具有以下结构的表.

I have a table in my database with the following structure.

ID   COMPANY_ID  Status
-----------------------
1       10         1 
2       10         2
3       12         2 
4       12         2 
5       12         1 
6       13         3 
7       14         3 
8       14         3 
9       10         1
10      10         2

我想将搜索结果按公司ID分组，并对每个状态进行计数，然后将其作为单独的列列出.

I want to group my results on company ID and count each status and list them as separate columns.

即

COMPANY_ID   Status 1   Status 2   Status 3
-------------------------------------------
10             2           2           0
12             1           2           0
13             0           0           1
14             0           0           2

我的问题是如何从表格中获得上述结果?并可能加入了公司表.

My question is how do I get the results above from my table? and probably join in with the company table.

尝试了几种可能性，但没有得到结果.

Tried several possibilities, but didn't get the results.

推荐答案

这种数据转换类型称为PIVOT.您有几种数据透视方式.

This type of data transformation is known as a PIVOT. There are several ways that you are pivot the data.

您可以将聚集函数与CASE表达式一起使用:

You can use an aggregate function with a CASE expression:

select company_id,
  sum(case when status = 1 then 1 else 0 end) status1,
  sum(case when status = 2 then 1 else 0 end) status2,
  sum(case when status = 3 then 1 else 0 end) status3
from yourtable
group by company_id;

请参见带有演示的SQL小提琴

从SQL Server 2005+开始，您可以使用

Starting in SQL Server 2005+ you can use the PIVOT function:

select company_id,
  [1] as Status1,
  [2] as Status2,
  [3] as Status3
from
(
  select company_id, status
  from yourtable
)src
pivot
(
  count(status)
  for status in ([1], [2], [3])
) piv

请参见带有演示的SQL提琴.

如果您具有已知数量的要转换为列的值，则上述两个版本都可以很好地工作.但是，如果未知，则可以使用动态SQL生成结果:

The two versions above work well if you have a known number of values to transform into columns. But if it is unknown, then you can use dynamic SQL to generate the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Status'+cast(status as varchar(10))) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT company_id,' + @cols + ' from 
             (
                select company_id, ''Status''+cast(status as varchar(10)) Status
                from yourtable
            ) x
            pivot 
            (
                count(Status)
                for Status in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL提琴.

全部给出结果:

| COMPANY_ID | STATUS1 | STATUS2 | STATUS3 |
--------------------------------------------
|         10 |       2 |       2 |       0 |
|         12 |       1 |       2 |       0 |
|         13 |       0 |       0 |       1 |
|         14 |       0 |       0 |       2 |

这篇关于分别计算一列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！