网友提出以下问题 ———————————————————————————————— select fundcode,AccumulatedUnitNV,UnitNV,EndDate from MF_NetValue
fundcode AccumulatedUnitNV UnitNV EndDate 0200092.1740 1.28902007-08-23 00:00:00 6300011.3908 1.39082007-08-23 00:00:00 0200092.1920 1.30702007-08-24 00:00:00 6300011.4100 1.41002007-08-24 00:00:00
表结果就是这样得..不过数据有多条 . 比如有23号,24号两个时间..我想再一个数据里能把两个数据得时间显示在一条数据里..
0200092.1740 1.28902007-08-23 00:00:00 2.19201.30702007-08-24 00:00:00
这样得形式得到结果集..多个fundcode 噢….我觉得只有存储过程做得到..但是不会..大家帮帮忙.
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ 解答： create table MF_NetValue(fundcode varchar(6),AccumulatedUnitNV decimal(18,4),UnitNV decimal(18,4),enddate datetime) insert MF_NetValue select ‘020009′,2.1740,1.2890,’2007-08-23 00:00:00′union all select ‘630001′,1.3908,1.3908,’2007-08-23 00:00:00′union all select ‘020009′,2.1920,1.3070,’2007-08-24 00:00:00′union all select ‘630001′,1.4100,1.4100,’2007-08-24 00:00:00′
select * from MF_NetValue declare @sql VARCHAR(8000)
DECLARE @StartDate DATETIME–查询开始日期 DECLARE @EndDate DATETIME–查询结束日期
DECLARE @tmpCondition VARCHAR(2000) DECLARE @tmpsql VARCHAR(8000) DECLARE @CurrDate DATETIME DECLARE @tmpDate VARCHAR(10)
set @StartDate=’2007-08-23′ set @EndDate=’2007-08-25′
SET @tmpCondition =’Where enddate>=”’+CONVERT(VARCHAR(10),@StartDate,120) +”’ AND enddate<=”’+CONVERT(VARCHAR(10),@EndDate,120) +”” SET @tmpsql=” SET @CurrDate = @StartDate while @CurrDate <= @EndDate begin SET @tmpDate = Convert(varchar(10),@CurrDate,121) select @tmpsql = @tmpsql + ‘, SUM(CASE WHEN Convert(varchar(10),enddate,121) = ”’+@tmpDate+”’ THEN AccumulatedUnitNV ELSE 0.00 END) AS ['+CONVERT(VARCHAR(10),@CurrDate,120)+'AccumulatedUnitNV]‘ + ‘, SUM(CASE WHEN Convert(varchar(10),enddate,121) = ”’+@tmpDate+”’ THEN UnitNV ELSE 0 END) AS ['+CONVERT(VARCHAR(10),@CurrDate,120)+'UnitNV]‘
select @CurrDate = @CurrDate +1 end SET @tmpsql= ‘Select fundcode ‘ +@tmpsql +’ FROM MF_NetValue ‘ + @tmpCondition +’ GROUP BY fundcode’ PRINT @tmpsql EXEC (@tmpsql) drop table MF_NetValue
(4 行受影响) fundcode AccumulatedUnitNV UnitNV enddate ——– ————————————— ————————————— ———————– 020009 2.1740 1.2890 2007-08-23 00:00:00.000 630001 1.3908 1.3908 2007-08-23 00:00:00.000 020009 2.1920 1.3070 2007-08-24 00:00:00.000 630001 1.4100 1.4100 2007-08-24 00:00:00.000
(4 行受影响)
Select fundcode , SUM(CASE WHEN Convert(varchar(10),enddate,121) = ‘2007-08-23′ THEN AccumulatedUnitNV ELSE 0.00 END) AS [2007-08-23AccumulatedUnitNV], SUM(CASE WHEN Convert(varchar(10),enddate,121) = ‘2007-08-23′ THEN UnitNV ELSE 0 END) AS [2007-08-23UnitNV], SUM(CASE WHEN Convert(varchar(10),enddate,121) = ‘2007-08-24′ THEN AccumulatedUnitNV ELSE 0.00 END) AS [2007-08-24AccumulatedUnitNV], SUM(CASE WHEN Convert(varchar(10),enddate,121) = ‘2007-08-24′ THEN UnitNV ELSE 0 END) AS [2007-08-24UnitNV], SUM(CASE WHEN Convert(varchar(10),enddate,121) = ‘2007-08-25′ THEN AccumulatedUnitNV ELSE 0.00 END) AS [2007-08-25AccumulatedUnitNV], SUM(CASE WHEN Convert(varchar(10),enddate,121) = ‘2007-08-25′ THEN UnitNV ELSE 0 END) AS [2007-08-25UnitNV] FROM MF_NetValue Where enddate>=’2007-08-23′ AND enddate<=’2007-08-25′ GROUP BY fundcode fundcode 2007-08-23AccumulatedUnitNV 2007-08-23UnitNV 2007-08-24AccumulatedUnitNV 2007-08-24UnitNV 2007-08-25AccumulatedUnitNV 2007-08-25UnitNV ——– ————————————— ————————————— ————————————— ————————————— ————————————— ————————————— 020009 2.1740 1.2890 2.1920 1.3070 0.0000 0.0000 630001 1.3908 1.3908 1.4100 1.4100 0.0000 0.0000
(2 行受影响)
转载于:https://www.cnblogs.com/Gaojier/archive/2010/04/08/2783590.html
