测试语句如下:
if object_id( 'tempdb..#ss ')> 0 drop table #ss
create table #ss
(
a_id int ,
day datetime ,
h_1 varchar(20),
h_1_h numeric(6,3),
h_2 varchar(20),
h_2_h numeric(6,3),
h_3 varchar(20),
h_3_h numeric(6,3),
)
insert into #ss select 1, '2007-07-02 ', 'A ',0.125, 'B ',0.125, 'D ',0.125
insert into #ss select 1, '2007-07-02 ', 'C ',0.125, 'B ',0.125, 'D ',0.125
insert into #ss select 1, '2007-07-02 ', 'C ',0.5,null,null,null,null
insert into #ss(a_id,day) select 2, '2007-07-01 '
select a_id,day,h_1,sum(h_1_h) h_1_h
from (
select a_id,day,h_1,h_1_h from #ss union all
select a_id,day,h_2,h_2_h from #ss union all
select a_id,day,h_3,h_3_h from #ss
) s
group by a_id,day,h_1
1 2007-07-02 00:00:00.000 A .125
1 2007-07-02 00:00:00.000 B .250
1 2007-07-02 00:00:00.000 C .625
1 2007-07-02 00:00:00.000 D .250