怎样实现以下查询？

[rgn]

有以下一个表，有company,kind字段
company kind
c1 k1
c2 k2
c1 k1
c1 k2
c2 k2
c2 k1


怎样得到以下查询结果

company k1 k2 total
c1 2 1 3
c2 1 2 3

[mgsray]

select company,sum( case kind when 'k1' then 1 else 0 end) as k1,
sum(case kind when 'k2' then 1 else 0 end) as k2,
count(1) as total as total
from table
group by company

[mgsray]

--多打了一个
select company,sum( case kind when 'k1' then 1 else 0 end) as k1,
sum(case kind when 'k2' then 1 else 0 end) as k2,
count(1) as total
from table
group by company

[Andy__Huang]

select company,k1=sum(case when kind='k1' then 1 else 0 end),
k2=sum(case when kind='k2' then 1 else 0 end),
total=count(*)
from tb group by company

[Andy__Huang]

create table tb(company varchar(5), kind varchar(5))
Insert into tb
select 'c1','k1'
union all select 'c2','k2'
union all select 'c1','k1'
union all select 'c1','k2'
union all select 'c2','k2'
union all select 'c2','k1'

select company,k1=sum(case when kind='k1' then 1 else 0 end),
k2=sum(case when kind='k2' then 1 else 0 end),
total=count(*)
from tb group by company

--結果
company k1 k2 total
----------------------------------
c1 2 1 3
c2 1 2 3

[rgn]

如果有很多个 k3,k4....　怎么办呢？

[lalakid]

SELECT DISTINCT
company,
k1 = (SELECT COUNT(*)
FROM 表
WHERE company = 表1.company
AND kind = 'k1'
GROUP BY company,kind),
k2 = (SELECT COUNT(*)
FROM 表
WHERE company = 表1.company
AND kind = 'k2'
GROUP BY company,kind),
total = (SELECT count(*)
FROM 表
WHERE company = 表1.company
GROUP BY company)
FROM 表 AS 表1

[Andy__Huang]

用動態sql語句

declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(kind)+']=sum(case kind when '''+max(kind)+''' then 1 else 0 end)'
from tb group by kind
print @sql

exec('select company'+@sql+',total=count(*) from tb group by company')

--結果
company k1 k2 total
----------------------------------
c1 2 1 3
c2 1 2 3

[lalakid]

假如不知道KIND有多少种，如下：
DECLARE @vchSql VARCHAR(8000),
@vchKind VARCHAR(40),
@iLoop INT,
@iCounter INT
SET @iLoop = 1
SET @vchSql = ''

SELECT id = IDENTITY(INT,1,1),
kind
INTO #Temp01
FROM (
SELECT DISTINCT
kind
FROM 表
) OK

SELECT @iCounter = @@ROWCOUNT


WHILE @iLoop <= @iCounter
BEGIN
SELECT @vchKind = kind
FROM #Temp01
WHERE id = @iLoop


SET @vchSql = @vchSql + @vchKind
+ '
= (SELECT COUNT(*)
FROM 表
WHERE company = 表1.company
AND kind = '+ '''' +@vchKind + '''' +
' GROUP BY company,kind),'
SET @iLoop = @iLoop + 1
END
SELECT @vchSql = 'SELECT DISTINCT
company,' +
@vchSql +
' total = (SELECT count(*)
FROM 表
WHERE company = 表1.company
GROUP BY company)
FROM 表 AS 表1 '

EXEC (@vchSql)