USE TEMPDB
GO

Create Table #temp
(
bianhao int Primary Key Identity(1,1) not null, 
chicun int,
didian nvarchar(5)
)
GO

INSERT INTO #temp
Select 17,'gz' Union
Select 15,'gz' Union
Select 17,'sh' Union
Select 19,'bj' Union
Select 19,'sh' Union
Select 17,'bj' Union
Select 17,'bj' 
GO

INSERT INTO #temp Values(17,'bj')

SELECT * from #temp

Select chicun,MAX(isnull(gz,0)) as gz,MAX(isnull(bj,0)) as bj,MAX(isnull(sh,0)) as sh From
(
Select chicun,
Case didian when 'gz' then Count(chicun) end as gz,
Case didian when 'bj' then Count(chicun) end as bj,
Case didian when 'sh' then Count(chicun) end as sh
From #temp
Group By chicun,didian
) as a Group By chicun