一个数据汇总，列合并的例子

-- 问题
库存表A（现有库存）
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY
A001            N-07          L      KG  800
B005            M01            O      KG  500
.....
收货记录表B
PARTNO VENDNO SALESTYPE ACTQTY INDATE(收货时间)
A001    N-07    L      250    5/01       
A001    N-07    L      250    6/10
A001    N-07    O      300    6/15
A001    N-07    L      300    6/25
A001    N-07    L      300    7/02
A001    N-66    L      250    7/08
A001    M88      L      280    7/1
B005    M01      O      300    5/05
B005    M01      O      200    6/01
......

为实现后进先出的目的，根据库存量取出最近的收货记录
比如物料A001 N-07 L 库存量是800，则取出最近>=800数量的收货记录
A001    N-07    L      300  7/02
A001    N-07    L      300  6/25
A001    N-07    L      250  6/10
                            300+300+250>=800
条件：1 B表的PARTNO VENDNO SALESTYPE 三个字段的值等于表A的
 

PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY  收货记录
A001            N-07          L    KG  800  7/02 300 6/25 300 6/10 250
B005            M01          O    KG  500  6/01 200  5/05 300

得到以上的结果就行，格式不限

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 解答

drop table tba,tbb
create table tbA(partno varchar(10), PARTDESC varchar(10), VENDNO varchar(10), GRADE varchar(10), SALESTYPE varchar(10), UNIT varchar(10), QTY int)
create table tbB(PARTNO varchar(10), VENDNO varchar(10), SALESTYPE varchar(10), ACTQTY int, INDATE datetime)
insert into tbA select 'A001', '', 'N-07', '', 'L', 'KG', 800
insert into tbA select 'B005', '', 'M01', '', 'O', 'KG',  500
insert into tbB select 'A001', 'N-07', 'L', 250    , '2008-05-01'       
insert into tbB select 'A001', 'N-07', 'L', 250    , '2008-06-10'
insert into tbB select 'A001', 'N-07', 'O',       300    , '2008-06-15'
insert into tbB select 'A001', 'N-07', 'L',       300    , '2008-06-25'
insert into tbB select 'A001', 'N-07', 'L',       300    , '2008-07-02'
insert into tbB select 'A001', 'N-66', 'L',       250    , '2008-07-08'
insert into tbB select 'A001', 'M88', 'L',      280    , '2008-07-01'
insert into tbB select 'B005', 'M01', 'O',      300    , '2008-05-05'
insert into tbB select 'B005', 'M01', 'O',       200    , '2008-06-01'

--drop function dbo.test
create
alter function dbo.test(@partno varchar(10), @vendno varchar(10), @SALESTYPE varchar(10), @actqty int)
returns @tbtest table(partno varchar(10), PARTDESC varchar(10), VENDO varchar(10), GRADE varchar(10), SALESTYPE varchar(10), UNIT varchar(10), QTY int,memo varchar(200))
as
begin
 declare @s int,@ss varchar(100)
 select @s = 0, @ss = '';
 select
 @ss = @ss + '  ' + case when @s < 800 then convert(varchar(10),indate,120) + ' ' + rtrim(actqty) else '' end,
 @s = @s + case when @s < @actqty then actqty else 0 end
 from tbB where partno = @partno and vendno = @vendno and SALESTYPE = @SALESTYPE order by indate desc
 --select @s,@ss
    insert into @tbtest
    select *,@ss from tbA where partno = @partno and vendno = @vendno and SALESTYPE = @SALESTYPE
    return
end
select b.* from tba a CROSS APPLY
dbo.test(a.partno, a.vendno, SALESTYPE, qty) b
--where a.partno = 'a001'