select 'A105' as prds,'20100501' as datebeg,'20100731' as dateend,5.6 as prdup into #temp
union all
select 'A105','20101105','20110505',6.9 union all
select 'A106','20100501','20100601',9.6 union all
select 'A106','20100601','20100630',3.6 union all
select 'A106','20100605','20100801',3.5 union all
select 'A106','20100801','20101101',5.5
数据如下
A105 20100501 20100731 5.6
A105 20101105 20110505 6.9
A106 20100501 20100601 9.6
A106 20100601 20100630 3.6
A106 20100605 20100801 3.5
A106 20100801 20101101 5.5
想转成横表得到以下数据
201005 201006 201007 201008 201009 201010 201011 201012 201101 201102 201103
A105 5.6 5.6 5.6 5.6 5.6 5.6 6.9 6.9 6.9 6.9 6.9
A106 3.6 9.6 9.6 5.5 5.5 5.5 5.5
当月up值没有,判断该月之前的月有没up值,有,取前月up值,没有,0
当月有二个up值,取最后一个up值
union all
select 'A105','20101105','20110505',6.9 union all
select 'A106','20100501','20100601',9.6 union all
select 'A106','20100601','20100630',3.6 union all
select 'A106','20100605','20100801',3.5 union all
select 'A106','20100801','20101101',5.5
数据如下
A105 20100501 20100731 5.6
A105 20101105 20110505 6.9
A106 20100501 20100601 9.6
A106 20100601 20100630 3.6
A106 20100605 20100801 3.5
A106 20100801 20101101 5.5
想转成横表得到以下数据
201005 201006 201007 201008 201009 201010 201011 201012 201101 201102 201103
A105 5.6 5.6 5.6 5.6 5.6 5.6 6.9 6.9 6.9 6.9 6.9
A106 3.6 9.6 9.6 5.5 5.5 5.5 5.5
当月up值没有,判断该月之前的月有没up值,有,取前月up值,没有,0
当月有二个up值,取最后一个up值
解决方案 »
- SQL2005 企业版维护计划里没有新建
- 统计出评论数的sql 语句???
- 我提交了更新语句,为什么提示命令行已执行,但是数据库却没有发生变化。
- 关于数据库端口问题求教?
- WIN2000 PRO 下安装的SQL SERVER是不是不能作为服务器?
- 在 SQL 中如何实现循环调用一个存储过程。
- sqlserver 如何判断硬盘上一个普通文件的存在
- 如何批量删除字段中的最后几个字符?
- 数据库连接失败的问题
- 请问VB+SQL Server2000的程序编写好后,如何打包?怎样把设计好的数据库自动安装到客户机器上?
- 安了SQL2005,可以再安VS2010吗?
- 求救··关于SqlServer2008安装了SQLManagementStudio后连接不上服务器实例~
select 'A105' as prds,'20100501' as datebeg,'20100731' as dateend,5.6 as prdup into #temp
union all
select 'A105','20101105','20110505',6.9 union all
select 'A106','20100501','20100601',9.6 union all
select 'A106','20100601','20100630',3.6 union all
select 'A106','20100605','20100801',3.5 union all
select 'A106','20100801','20101101',5.5select * from #temp;WITH T AS (
select MIN(DateBeg) as DateBeg ,MAX(DateEnd)DateEnd from #TEMP)
,T1 AS (
SELECT CAST (DATEBEG AS DATETIME) BEG ,CAST (DATEEND AS DATETIME) EN FROM T)
,T2 AS (SELECT DATEDIFF (MM,BEG,EN) MON FROM T1)
,T3 AS (SELECT BEG,MON FROM T1,T2
UNION ALL
SELECT DATEADD(MM,1, BEG ),MON-1
FROM T3 WHERE MON>0)
,T4 AS(SELECT PRDS ,CAST (DATEBEG AS DATETIME) BEG
,CAST (DATEEND AS DATETIME) EN ,PRDUP PRDUP ,ROW_NUMBER ()OVER(ORDER BY GETDATE()) SN FROM #TEMP)
,T5 AS (SELECT DISTINCT PRDS FROM #TEMP)
,T6 AS (SELECT BEG,MON,PRDS PRDS FROM T3,T5)
,T7 AS (SELECT T6.*,T4.PRDS PRDS1 ,T4.PRDUP,T4.SN FROM T6 LEFT JOIN T4 ON (T6.BEG BETWEEN T4.BEG AND T4.EN) AND T6.PRDS =T4.PRDS )
,T8 AS (SELECT * ,ROW_NUMBER ()OVER(PARTITION BY PRDS ,BEG ORDER BY SN DESC)SN1 FROM T7)
,T9 AS (SELECT * FROM T8 WHERE SN1=1)
,T10 AS (SELECT BEG BEG,PRDS,PRDUP,MON FROM T9 JOIN T ON T9.BEG =T.DateBeg
UNION ALL
SELECT T9.BEG ,T9.PRDS,ISNULL(T9.PRDUP,T10.PRDUP ),T9.MON FROM T10 JOIN T9 ON T10.MON =T9.MON+1 AND T9.PRDS =T10.PRDS
)
SELECT * FROM T10 BEG PRDS PRDUP MON
2010-05-01 00:00:00.000 A105 5.6 12
2010-05-01 00:00:00.000 A106 9.6 12
2010-06-01 00:00:00.000 A106 3.6 11
2010-07-01 00:00:00.000 A106 3.5 10
2010-08-01 00:00:00.000 A106 5.5 9
2010-09-01 00:00:00.000 A106 5.5 8
2010-10-01 00:00:00.000 A106 5.5 7
2010-11-01 00:00:00.000 A106 5.5 6
2010-12-01 00:00:00.000 A106 5.5 5
2011-01-01 00:00:00.000 A106 5.5 4
2011-02-01 00:00:00.000 A106 5.5 3
2011-03-01 00:00:00.000 A106 5.5 2
2011-04-01 00:00:00.000 A106 5.5 1
2011-05-01 00:00:00.000 A106 5.5 0
2010-06-01 00:00:00.000 A105 5.6 11
2010-07-01 00:00:00.000 A105 5.6 10
2010-08-01 00:00:00.000 A105 5.6 9
2010-09-01 00:00:00.000 A105 5.6 8
2010-10-01 00:00:00.000 A105 5.6 7
2010-11-01 00:00:00.000 A105 5.6 6
2010-12-01 00:00:00.000 A105 6.9 5
2011-01-01 00:00:00.000 A105 6.9 4
2011-02-01 00:00:00.000 A105 6.9 3
2011-03-01 00:00:00.000 A105 6.9 2
2011-04-01 00:00:00.000 A105 6.9 1
2011-05-01 00:00:00.000 A105 6.9 0以后是日期转文本,行转列,不写了