22,207
社区成员
发帖
与我相关
我的任务
分享
create table #tabletemp
(
plancode varchar(30),
qty int,
date smalldatetime,
ivno varchar(20),
outqty int
)
insert into #tabletemp values( 'P0001', '100', '2010-5-27' , 'v0001' ,10)
insert into #tabletemp values( 'P0001', '100', '2010-5-28' , 'v0002' ,20)
insert into #tabletemp values( 'P0002', '50', '2010-5-2' , 'v0003' ,20)
insert into #tabletemp values( 'P0002', '50', '2010-5-3' , 'v0004' ,10)
insert into #tabletemp values( 'P0002', '50', '2010-5-4' , 'v0005' ,10)
---select * from #tabletemp
---drop table #tabletemp
select
plancode,
qty,
max(case id when 1 then date else '' end) as 日期,
max(case id when 2 then date else '' end) as 日期,
max(case id when 3 then date else '' end) as 日期,
max(case id when 1 then ivno else '' end) as 发票号,
max(case id when 2 then ivno else '' end) as 发票号,
max(case id when 3 then ivno else '' end) as 发票号,
max(case id when 1 then outqty else 0 end) as 走货数量,
max(case id when 2 then outqty else 0 end) as 走货数量,
max(case id when 3 then outqty else 0 end) as 走货数量
from
(
select id=row_number()over(partition by plancode order by getdate()),* from #tabletemp
)t
group by
plancode,
qty
/*plancode qty 日期 日期 日期 发票号 发票号 发票号 走货数量 走货数量 走货数量
------------------------------ ----------- ----------------------- ----------------------- ----------------------- -------------------- -------------------- -------------------- ----------- ----------- -----------
P0002 50 2010-05-02 00:00:00 2010-05-03 00:00:00 2010-05-04 00:00:00 v0003 v0004 v0005 20 10 10
P0001 100 2010-05-27 00:00:00 2010-05-28 00:00:00 1900-01-01 00:00:00 v0001 v0002 10 20 0
(2 行受影响)
*/