if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id int,time nvarchar(22),count int)
insert into [TB]
select 1,'8:00-9:00',60 union all
select 2,'9:00-10:00',80 union all
select 3,'10:00-11:00',40 union all
select 1,'9:00-10:00',69

select * from [TB]

select ID,
MAX(case when time ='8:00-9:00' then [COUNT] else 0 end ) as '8:00-9:00',
MAX(case when time ='9:00-10:00' then [COUNT] else 0 end ) as '9:00-10:00',
MAX(case when time ='10:00-11:00' then [COUNT] else 0 end ) as '10:00-11:00'
from TB
group by id

/*
ID          8:00-9:00   9:00-10:00  10:00-11:00
----------- ----------- ----------- -----------
1           60          69          0
2           0           80          0
3           0           0           40

(3 行受影响)

------解决方案--------------------
SQL code

IF OBJECT_ID('tab') IS NOT NULL DROP TABLE tab
CREATE TABLE TAB(id INT,[TIME] VARCHAR(20),[COUNT] INT)
INSERT INTO tab
SELECT 1, '8：00-9：00', 60 UNION ALL
SELECT 2, '9：00-10：00', 80 UNION ALL
SELECT 3, '10：00-11：00', 40 UNION  ALL
SELECT 1, '9：00-10：00' ,69
DECLARE @timeProperty VARCHAR(max)
declare @sql varchar(max)
SET @timeProperty=''
SELECT @timeProperty=@timeProperty+','+'['+[time]+']' FROM tab GROUP BY [time]
SET @timeProperty=STUFF(@timeProperty,1,1,'')
set @sql='SELECT * FROM tab
            PIVOT ( SUM([COUNT]) FOR [time] IN ('+@timeProperty+')  ) AS p'
EXEC (@sql)
/*
id          10：00-11：00 8：00-9：00   9：00-10：00
----------- ----------- ----------- -----------
1           NULL        60          69
2           NULL        NULL        80
3           40          NULL        NULL
*/

------解决方案--------------------
探讨


求教达人个问题，如果ID 有N个呢?也这样写？