CSDN网友提出以下问题
————————————————————————————————
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 行受影响)
