27,580
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ORDER_NO varchar(6),PAET_NO varchar(7),QTY int,[DATETIME] datetime)
insert into #T
select 'A80301','33A-001',10,'2008-06-21' union all
select 'A80301','33A-001',20,'2008-06-22' union all
select 'A80301','33A-002',5,'2008-06-22' union all
select 'A80302','33A-001',30,'2008-06-23'
declare @minDate datetime
declare @sql varchar(8000)
select @minDate=min([DateTime]) from #T
set @sql='select ORDER_NO,PAET_NO'
while @minDate<=getdate()
begin
select @sql=@sql+',sum(case when convert(varchar(10),[DATETIME],120)='''
+convert(varchar(10),@minDate,120)+''' then QTY else 0 end) ['
+ltrim(month(@minDate))+'月'+ltrim(day(@minDate))+'日'+']'
set @minDate=dateadd(day,1,@minDate)
end
exec (@sql+' from #T group by ORDER_NO,PAET_NO')
/*
ORDER_NO PAET_NO 6月21日 6月22日 6月23日 6月24日 6月25日 6月26日 6月27日 6月28日
-------- ------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A80301 33A-001 10 20 0 0 0 0 0 0
A80302 33A-001 0 0 30 0 0 0 0 0
A80301 33A-002 0 5 0 0 0 0 0 0
(3 行受影响)
*/
--原帖.这个代码是我写的.没错.
http://topic.csdn.net/u/20080628/09/be3183ea-a277-4cb8-a7a6-4cfb16641f43.html
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ORDER_NO varchar(6),PAET_NO varchar(7),QTY int,[DATETIME] datetime)
insert into #T
select 'A80301','33A-001',10,'2008-06-21' union all
select 'A80301','33A-001',20,'2008-06-22' union all
select 'A80301','33A-002',5,'2008-06-22' union all
select 'A80302','33A-001',30,'2008-06-23'
declare @minDate datetime
declare @sql varchar(8000)
select @minDate=min([DateTime]) from #T
set @sql='select ORDER_NO,PAET_NO'
while @minDate<=getdate()
begin
select @sql=@sql+',sum(case when convert(varchar(10),[DATETIME],120)='''+convert(varchar(10),@minDate,120)+''' then QTY else 0 end) ['+convert(varchar(10),@minDate,111)+']'
set @minDate=dateadd(day,1,@minDate)
end
exec (@sql+' from #T group by ORDER_NO,PAET_NO')
/*
ORDER_NO PAET_NO 2008/06/21 2008/06/22 2008/06/23 2008/06/24 2008/06/25 2008/06/26 2008/06/27 2008/06/28
-------- ------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A80301 33A-001 10 20 0 0 0 0 0 0
A80302 33A-001 0 0 30 0 0 0 0 0
A80301 33A-002 0 5 0 0 0 0 0 0
(3 行受影响)
*/