表:gl_budg 数据举例如下:
ac_code deptno de_ap01 de_ap02 de_ap03 de_ap04 de_ap05
990002 110101 212500 212500 722500 722500 935000想要的结果如下:
ac_code deptno month money
990002 110101 de_ap01 212500
990002 110101 de_ap02 212500
990002 110101 de_ap03 722500
990002 110101 de_ap04 722500
990002 110101 de_ap05 933500
请教各位高手如何才能实现?
ac_code deptno de_ap01 de_ap02 de_ap03 de_ap04 de_ap05
990002 110101 212500 212500 722500 722500 935000想要的结果如下:
ac_code deptno month money
990002 110101 de_ap01 212500
990002 110101 de_ap02 212500
990002 110101 de_ap03 722500
990002 110101 de_ap04 722500
990002 110101 de_ap05 933500
请教各位高手如何才能实现?
--生成测试数据
create table tb_Test(c_No varchar(20),c_Office varchar(10),c_Time varchar(10))
insert into tb_Test select '13509998888','翠星营业','2005-01'
insert into tb_Test select '13509998888','翠星营业','2005-01'
insert into tb_Test select '13509998888','翠星营业','2005-02'
insert into tb_Test select '13509998888','翠星营业','2005-03'
insert into tb_Test select '13509998888','东南营业','2005-03'
insert into tb_Test select '13509998888','东南营业','2005-03'
insert into tb_Test select '13509998888','东南营业','2005-04'
insert into tb_Test select '13509998888','东南营业','2005-04'
insert into tb_Test select '13509998888','东南营业','2005-04'
insert into tb_Test select '13509998888','东南营业','2005-04'
insert into tb_Test select '13509998888','东南营业','2005-04'--执行处理过程
select identity(int,1,1) as rowid,0 as rid,* into #T from tb_Test
update a set rid=(select count(*) from #t where c_No=a.c_No and c_Time=a.c_Time and rowid<=a.rowid) from #t adeclare @s varchar(8000)
set @s = ''select @s = @s+',c_Time_Office'+a.mon+'=max(case right(c_Time,2) when '''+a.mon+''' then c_Office end)'
from (select distinct mon=right(c_Time,2) from #T) a order by a.monset @s = 'select c_No'+@s+' from #T group by c_No,rid order by rid'exec(@s)
--删除测试数据
drop table tb_Test,#T--输出结果
/*
c_No c_Time_Office1 c_Time_Office2 c_Time_Office3 c_Time_Office4
13509998888 翠星营业 翠星营业 翠星营业 东南营业
13509998888 翠星营业 NULL 东南营业 东南营业
13509998888 NULL NULL 东南营业 东南营业
13509998888 NULL NULL NULL 东南营业
13509998888 NULL NULL NULL 东南营业
*/