行转列问题51CTO博客 - 亚美娱乐

行转列问题51CTO博客

2019-01-04 21:36:42 | 作者: 平安 | 标签: 数据,问题,队伍 | 浏览: 2015

前些天在论坛上看到老三提出的一个问题是关于队伍转化。曾经在网上也看过一些队伍转化的问题,不过都是只简略的把队伍做个倒置。而他的要求是把某一年的数据转化为一列。想了会觉得可以用动态创立列来完成。 源数据: Aug 792.50 2005
May 720.00 2006
Oct 897.50 2006
Sep 1080.00 2006
Sep 549.00 2004
Apr 855.50 2006
Mar 494.50 2005
Jul 803.50 2005
Feb 283.00 2005
Dec 758.50 2005
Mar 828.50 2006
Aug 612.50 2004
Aug 926.00 2006
Dec 412.50 2004
Dec 843.00 2006
Jul 669.00 2004
Jul 894.00 2006
Jun 707.50 2005
Nov 634.50 2005
Jan 599.50 2005
Oct 874.00 2005
May 647.50 2005
Feb 285.50 2006
Feb 2.00 2004
Jun 31.00 2004
Jun 706.00 2006
Jan 646.00 2006
Nov 949.00 2006
Nov 558.00 2004
Apr 698.50 2005
Oct 616.50 2004
Sep 864.50 2005
成果如图: 下面是我的完成 --将表中数据按年显现在不同列中
--author:boyi55 date:2007-05-15
----------------------------------
--原表数据
select * from summary
---------------------------------------------------------------------------------------------
--将原表数据排序写入暂时表
select isnull(month+-+right(year,2),TOTAL)as month,sum(money)as money,year
into #boyi55
from summary
group by year,month with rollup
order by year asc,month asc
--删去最终的一切数据的总汇总行
delete from #boyi55
where money in (select max(money) from #boyi55)
--检查暂时表数据
select * from #boyi55
-------------------------------------------------------------------------------------------
--生成最终数据表
declare @a int,@b int,@c int,@d varchar(500)
select @a=min(year),@b=max(year) from #boyi55
set @c=@a
--创立成果表
create table boyi5555(month varchar(20))
--插入排序月份数据
insert into boyi5555 values(jan)
insert into boyi5555 values(feb)
insert into boyi5555 values(mar)
insert into boyi5555 values(apr)
insert into boyi5555 values(may)
insert into boyi5555 values(jun)
insert into boyi5555 values(jul)
insert into boyi5555 values(aug)
insert into boyi5555 values(sep)
insert into boyi5555 values(oct)
insert into boyi5555 values(nov)
insert into boyi5555 values(dec)
insert into boyi5555 values(TOTAL)
while @c<=@b
begin
--循环增加列
set @d=alter table boyi5555 add month+cast(@c as varchar(4))+ varchar(20)+char(10)+char(13)
+alter table boyi5555 add money+cast(@c as varchar(4))+ numeric(8,2)
exec(@d)
--更新新列数据
set @d=update boyi5555 set month+cast(@c as varchar(4))+=b.month,money++cast(@c as varchar(4))+=b.money from (select month ,money from #boyi55 where year=+cast(@c as varchar(20))+) as b where left(boyi5555.month,3)=left(b.month,3)
exec(@d)
set @c=@c+1
end
--删去中心列
alter table boyi5555 drop column month
--显现成果
select * from boyi5555
------------------------------
month2004            money2004  month2005            money2005  month2006            money2006 
-------------------- ---------- -------------------- ---------- -------------------- ----------
NULL                 NULL       Jan-05               599.50     Jan-06               646.00
Feb-04               2.00       Feb-05               283.00     Feb-06               285.50
NULL                 NULL       Mar-05               494.50     Mar-06               828.50
NULL                 NULL       Apr-05               698.50     Apr-06               855.50
NULL                 NULL       May-05               647.50     May-06               720.00
Jun-04               31.00      Jun-05               707.50     Jun-06               706.00
Jul-04               669.00     Jul-05               803.50     Jul-06               894.00
Aug-04               612.50     Aug-05               792.50     Aug-06               926.00
Sep-04               549.00     Sep-05               864.50     Sep-06               1080.00
Oct-04               616.50     Oct-05               874.00     Oct-06               897.50
Nov-04               558.00     Nov-05               634.50     Nov-06               949.00
Dec-04               412.50     Dec-05               758.50     Dec-06               843.00
TOTAL                3450.50    TOTAL                8158.00    TOTAL                9631.00


-----------------------------------
--完毕删去实验数据
drop table #boyi55
drop table boyi5555
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章