--如下图,代码的任务就是将图一转为图二
--此功能在PB中的交叉报表也可实现
--编写此段代码是因为今天遇到客户有这个需求,自己觉得可以挑战一下,就尝试做下来了
代码如下:
-
create
table #patient_result_temp ( -
patient_code
nvarchar ( 100 ), -
date_test
datetime , -
item_code
nvarchar ( 100 ), -
test_value2
nvarchar ( 100 ) -
)
-
create
table #patient_result_target ( -
patient_code
nvarchar ( 100 ), -
date_test
datetime , -
RBC
nvarchar ( 50 ), -
WBC
nvarchar ( 50 ), -
PLT
nvarchar ( 50 ) -
)
-
-
-
insert
into #patient_result_temp select a . patient_code , a . date_test , b . item_code , b . test_value2 from ut_check_patient a , ut_check_result b where a . date_test = b . test_date and a . ybh = b . ybh and item_code in ( 'RBC' , 'WBC' , 'PLT' ) and a . date_test > '2013-10-21' and a . patient_code > '' -
-
declare
my_cursor cursor -
for
select * from #patient_result_temp -
open
my_cursor -
declare
@p_code nvarchar ( 100 ), @d_test datetime , @i_code nvarchar ( 100 ), @t_value2 nvarchar ( 100 ) -
fetch
next from my_cursor into @p_code , @d_test , @i_code , @t_value2 -
while
( @@fetch_status = 0 ) -
begin -
-
if @i_code = 'RBC' -
begin -
-
if ( select count (*) from #patient_result_target where patient_code = @p_code and date_test = @d_test ) <= 0 -
begin -
insert into #patient_result_target values ( @p_code , @d_test , @t_value2 , '' , '' ) -
end -
else -
begin -
update #patient_result_target -
set RBC = @t_value2 -
where patient_code = @p_code and date_test = @d_test -
end -
end -
-
if @i_code = 'WBC' -
begin -
-
if ( select count (*) from #patient_result_target where patient_code = @p_code and date_test = @d_test ) <= 0 -
begin -
insert into #patient_result_target values ( @p_code , @d_test , @t_value2 , '' , '' ) -
end -
else -
begin -
update #patient_result_target -
set WBC = @t_value2 -
where patient_code = @p_code and date_test = @d_test -
end -
end -
if @i_code = 'PLT' -
begin -
-
if ( select count (*) from #patient_result_target where patient_code = @p_code and date_test = @d_test ) <= 0 -
begin -
insert into #patient_result_target values ( @p_code , @d_test , '' , '' , @t_value2 ) -
end -
else -
begin -
update #patient_result_target -
set PLT = @t_value2 -
where patient_code = @p_code and date_test = @d_test -
end -
end -
-
-
-
fetch next from my_cursor into @p_code , @d_test , @i_code , @t_value2 -
end -
-
-
close
my_cursor -
deallocate
my_cursor -
-
select
* from #patient_result_target -
drop
table #patient_result_target -
drop
table #patient_result_temp