求教：SQL 交叉表的问题

Question

SQL语句为：
select stcd,tm form st_river 
where stcd=61801700 and year(tm)=2011 and month(tm)=12 and day(tm)=23
查询的结果如下：
STCD TM Z
61801700 2011-12-23 00:00:00.000 171.760
61801700 2011-12-23 01:00:00.000 171.710
61801700 2011-12-23 02:00:00.000 171.670
61801700 2011-12-23 03:00:00.000 171.630
61801700 2011-12-23 04:00:00.000 171.590
61801700 2011-12-23 05:00:00.000 171.550
61801700 2011-12-23 06:00:00.000 171.520
61801700 2011-12-23 07:00:00.000 171.480
61801700 2011-12-23 08:00:00.000 171.450
61801700 2011-12-23 09:00:00.000 171.420
61801700 2011-12-23 10:00:00.000 171.390
61801700 2011-12-23 11:00:00.000 171.360
61801700 2011-12-23 12:00:00.000 171.340
61801700 2011-12-23 13:00:00.000 171.320
61801700 2011-12-23 14:00:00.000 171.300
61801700 2011-12-23 15:00:00.000 171.290
61801700 2011-12-23 16:00:00.000 171.270
61801700 2011-12-23 17:00:00.000 171.260
61801700 2011-12-23 18:00:00.000 171.260
61801700 2011-12-23 19:00:00.000 171.260
61801700 2011-12-23 20:00:00.000 171.260
61801700 2011-12-23 21:00:00.000 171.270
61801700 2011-12-23 22:00:00.000 171.290
61801700 2011-12-23 23:00:00.000 171.320  现在我想得到如下的结果：
STCD                  TM
61801700    0   1    2   23
171.76 171.71 171.67 ...........       171.32
0、1、2...23分别代表0时至23时
请问怎么实现，谢谢！

Answer 1

select
  STCD,
  sum(case when datepart(hh,tm)=1 then z else 0 end) as '1',
  sum(case when datepart(hh,tm)=2 then z else 0 end) as '2',.......
from
  tb
group by
  stcd