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)计划号 数量 日期 发票号 走货数量
P0001 100 '2010-5-27' 'v0001' 10
P0001 100 '2010-5-28' 'v0002' 20
P0002 50 '2010-5-2' 'v0003' 20
P0002 50 '2010-5-3' 'v0004' 10
P0002 50 '2010-5-4' 'v0005' 20--要实现的效果,如果还有话的,还要重复。
计划号 数量 日期 发票号 走货数量 日期 发票号 走货数量 ...P0001 100 '2010-5-27' 'v0001' 10 '2010-5-28' 'v0002' 20
P0002 50 '2010-5-2' 'v0003' 20 '2010-5-3' 'v0004' 10 '2010-5-4' 'v0005' 20
加一个排序列 再转 2005用partition by ...order by ...
(
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 #tabletempselect
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 行受影响)
*/
insert into #tabletemp values( 'P0001', '100', '2010-5-28' , 'v0002' ,20)
insert into #tabletemp values( 'P0001', '100', '2010-5-29' , 'v0008' ,11)
insert into #tabletemp values( 'P0001', '100', '2010-5-230' , 'v0009' ,12)
... ...