select year as 日期,month01 as 销售数量 from 表 where year='200601' union select year as 日期,month02 as 销售数量 from 表 where year='200602' union select year as 日期,month03 as 销售数量 from 表 where year='200603' union ..... union select year as 日期,month12 as 销售数量 from 表 where year='200612'
select year as 日期 month01 as 销售数量 from 表 union select year as 日期 month02 as 销售数量 from 表 . . . .
修正一下: select year||'-01' as 日期,month01 as 销售数量 from 表 where year='2006' union select year||'-02' as 日期,month02 as 销售数量 from 表 where year='2006' union select year||'-03' as 日期,month03 as 销售数量 from 表 where year='2006' union ..... union select year||'-12' as 日期,month12 as 销售数量 from 表 where year='2006'
再修正一下: select aa.日期,aa.销售数量 from ( select year||'01' as 日期,month01 as 销售数量 from 表 where year='2006' union select year||'02' as 日期,month02 as 销售数量 from 表 where year=to_char(sysdate,'yyyy') union select year||'03' as 日期,month03 as 销售数量 from 表 where year=to_char(sysdate,'yyyy') union ..... union select year||'12' as 日期,month12 as 销售数量 from 表 where year=to_char(sysdate,'yyyy') ) aa where aa.日期=to_char(sysdate,'yyyymm')
union
select year as 日期,month02 as 销售数量 from 表 where year='200602'
union
select year as 日期,month03 as 销售数量 from 表 where year='200603'
union
.....
union
select year as 日期,month12 as 销售数量 from 表 where year='200612'
month01 as 销售数量
from 表
union
select year as 日期
month02 as 销售数量
from 表
.
.
.
.
select year||'-01' as 日期,month01 as 销售数量 from 表 where year='2006'
union
select year||'-02' as 日期,month02 as 销售数量 from 表 where year='2006'
union
select year||'-03' as 日期,month03 as 销售数量 from 表 where year='2006'
union
.....
union
select year||'-12' as 日期,month12 as 销售数量 from 表 where year='2006'
where year=to_char(sysdate,'yyyy')
我在sql server中建了个DTS包,用代理每月从ORACLE数据库取出当月销量。
语句要求以原表列名monthXX后两位数字=datepart(month,getdate())为条件
select aa.日期,aa.销售数量
from
(
select year||'01' as 日期,month01 as 销售数量 from 表 where year='2006'
union
select year||'02' as 日期,month02 as 销售数量 from 表 where year=to_char(sysdate,'yyyy')
union
select year||'03' as 日期,month03 as 销售数量 from 表 where year=to_char(sysdate,'yyyy')
union
.....
union
select year||'12' as 日期,month12 as 销售数量 from 表 where year=to_char(sysdate,'yyyy')
) aa
where aa.日期=to_char(sysdate,'yyyymm')
我在sql server中建了个DTS包,用代理每月从ORACLE数据库取出当月销量。
语句要求以原表列名monthXX后两位数字=datepart(month,getdate())为条件--这好像也和你的行列变换没有什么关系吧,
你不是固定有12个月吗?
而且这些字段都是固定的吧?
还有必要考虑上面的东东吗?
month01 as 销售数量
from 表
union
select year || '-02' as 日期
month02 as 销售数量
from 表 where
.
.
.
.--这样不就OK?