这种类型的数据转换称为PIVOT。有几种数据透视图。
您可以将聚合函数与CASE表达式一起使用：
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 Server 2005+开始，您可以使用以下PIVOT功能：
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 Fiddle。
如果您有已知数量的要转换为列的值，则上面的两个版本会很好地工作。但是，如果未知，则可以使用动态sql生成结果：
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 Fiddle。
全部给出结果：
| COMPANY_ID | STATUS1 | STATUS2 | STATUS3 |
--------------------------------------------
| 10 | 2 | 2 | 0 |
| 12 | 1 | 2 | 0 |
| 13 | 0 | 0 | 1 |
| 14 | 0 | 0 | 2 |
602392714
清零编程群