一DataTable合并相同行数据出现的问题

Question

现在从数据表中得到一记录某病人一天中生命体征的Datatable：
            
检测项目             检测时间             检测数值
                    
 温度            2012-6-6 6：00：00         36.7
 温度            2012-6-6 7：00：00         36.9
 温度            2012-6-6 8：00：00         37.0
 ...
 收缩压          2012-6-6 2：00：00         80
 收缩压          2012-6-6 3：00：00         85
 收缩压          2012-6-6 4：00：00         82
 ...
 舒张压           2012-6-6 5：00：00        140
 舒张压           2012-6-6 6：00：00        146
 舒张压           2012-6-6 7：00：00        130
 ...  
为了方便打印出来，使用水晶报表，于是想把从数据库得到的DataTable 转变为：检测项目      0  1  2     3     4       5     6     7    8     9 10 11 12 13 14 15 16 17 18 19 20 21 22 23温度           ............................  36.7 36.9 37.0    ............................................
收缩压        ....  80     85   82   .....................................................................
舒张压        .......................  140  146   130   ..................................................
....
...
.新的DataTable中列名为自己添加的从0到23，意思是24个小时，每个项目名称在表中只有一行，其中每个记录数值对应上面列名，请问要如何得到这张新的DataTable,或者有什么更好的方法让水晶报表这样显示出来
 检测项目还有其他  不一定每个小时都有记录 没记录的打印出来时只需要为空着就行

Answer 1

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([检测项目] varchar(6),[检测时间] datetime,[检测数值] numeric(4,1))
insert [tb]
select '温度','2012-6-6 6:00:00',36.7 union all
select '温度','2012-6-6 7:00:00',36.9 union all
select '温度','2012-6-6 8:00:00',37.0 union all
select '收缩压','2012-6-6 2:00:00',80 union all
select '收缩压','2012-6-6 3:00:00',85 union all
select '收缩压','2012-6-6 4:00:00',82 union all
select '舒张压','2012-6-6 5:00:00',140 union all
select '舒张压','2012-6-6 6:00:00',146 union all
select '舒张压','2012-6-6 7:00:00',130
goselect 检测项目,
  [0]=max(case when datepart(hh,检测时间)=0 then 检测数值 end),
  [1]=max(case when datepart(hh,检测时间)=1 then 检测数值 end),
  [2]=max(case when datepart(hh,检测时间)=2 then 检测数值 end),
  [3]=max(case when datepart(hh,检测时间)=3 then 检测数值 end),
  [4]=max(case when datepart(hh,检测时间)=4 then 检测数值 end),
  [5]=max(case when datepart(hh,检测时间)=5 then 检测数值 end),
  [6]=max(case when datepart(hh,检测时间)=6 then 检测数值 end),
  [7]=max(case when datepart(hh,检测时间)=7 then 检测数值 end),
  [8]=max(case when datepart(hh,检测时间)=8 then 检测数值 end),
  [9]=max(case when datepart(hh,检测时间)=9 then 检测数值 end),
  [10]=max(case when datepart(hh,检测时间)=10 then 检测数值 end),
  [11]=max(case when datepart(hh,检测时间)=11 then 检测数值 end),
  [12]=max(case when datepart(hh,检测时间)=12 then 检测数值 end),
  [13]=max(case when datepart(hh,检测时间)=13 then 检测数值 end),
  [14]=max(case when datepart(hh,检测时间)=14 then 检测数值 end),
  [15]=max(case when datepart(hh,检测时间)=15 then 检测数值 end),
  [16]=max(case when datepart(hh,检测时间)=16 then 检测数值 end),
  [17]=max(case when datepart(hh,检测时间)=17 then 检测数值 end),
  [18]=max(case when datepart(hh,检测时间)=18 then 检测数值 end),
  [19]=max(case when datepart(hh,检测时间)=19 then 检测数值 end),
  [20]=max(case when datepart(hh,检测时间)=20 then 检测数值 end),
  [21]=max(case when datepart(hh,检测时间)=21 then 检测数值 end),
  [22]=max(case when datepart(hh,检测时间)=22 then 检测数值 end),
  [23]=max(case when datepart(hh,检测时间)=23 then 检测数值 end)
from
  tb
group by 检测项目/**
检测项目   0                                       1                                       2                                       3                                       4                                       5                                       6                                       7                                       8                                       9                                       10                                      11                                      12                                      13                                      14                                      15                                      16                                      17                                      18                                      19                                      20                                      21                                      22                                      23
------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
收缩压    NULL                                    NULL                                    80.0                                    85.0                                    82.0                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL
舒张压    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    140.0                                   146.0                                   130.0                                   NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL
温度     NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    36.7                                    36.9                                    37.0                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL(3 行受影响)
**/

Answer 2

具体在WinForm中我要怎么实现啊

Answer 3

用pivot也可以
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([检测项目] varchar(6),[检测时间] datetime,[检测数值] numeric(4,1))
insert [tb]
select '温度','2012-6-6 6:00:00',36.7 union all
select '温度','2012-6-6 7:00:00',36.9 union all
select '温度','2012-6-6 8:00:00',37.0 union all
select '收缩压','2012-6-6 2:00:00',80 union all
select '收缩压','2012-6-6 3:00:00',85 union all
select '收缩压','2012-6-6 4:00:00',82 union all
select '舒张压','2012-6-6 5:00:00',140 union all
select '舒张压','2012-6-6 6:00:00',146 union all
select '舒张压','2012-6-6 7:00:00',130
goselect * FROM(
     select [检测项目],检测时间=datepart(hh,检测时间),[检测数值] from [tb]) A
  pivot (sum([检测数值])for [检测时间] in ([1],[2],[3],[4],[5],[6],[7],[8],[9])) B

Answer 4

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([检测项目] varchar(6),[检测时间] datetime,[检测数值] numeric(4,1))
insert [tb]
select '温度','2012-6-6 6:00:00',36.7 union all
select '温度','2012-6-6 7:00:00',36.9 union all
select '温度','2012-6-6 8:00:00',37.0 union all
select '收缩压','2012-6-6 2:00:00',80 union all
select '收缩压','2012-6-6 3:00:00',85 union all
select '收缩压','2012-6-6 4:00:00',82 union all
select '舒张压','2012-6-6 5:00:00',140 union all
select '舒张压','2012-6-6 6:00:00',146 union all
select '舒张压','2012-6-6 7:00:00',130
goselect * FROM(
     select [检测项目],检测时间=datepart(hh,检测时间),[检测数值] from [tb]) A
  pivot (sum([检测数值])for [检测时间] in ([1],[2],[3],[4],[5],[6],[7],[8],[9])) B