SELECT  a.ReceiptNO,
                                    a.ReceiptDate,
                                    a.Sender,
                                    a.Receiver,
                                    a.Maker,
                                    a.Remark,
                                    a.LatestReceiveDate,
                                    a.TransportationNo,
                                    a.DeliveryMan,
                                    a.PdtName,
                                    a.NumUnit,
                                    a.DeclarationNO,
                                    b.Name as SenderName,
                                    b.ShortName as ShortSenderName,
                                    c.Name as ReceiverName,
                                    c.ShortName as ShortReceiverName,
                                    dbo.getReceiptInvoice(a.ReceiptNO) As InvoiceNos
                              FROM tblBizReceiptBill a left join 
                                    tblCRMManagementUnit b on a.Sender = b.Id left join 
                                    tblCRMCustomer c on a.Receiver = c.Id  WHERE 1=1 

  USE [tcliedms]
GO
/****** 对象:  UserDefinedFunction [dbo].[getReceiptInvoice]    脚本日期: 04/01/2012 09:28:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   function    [dbo].[getReceiptInvoice](@no nvarchar(50))   
  RETURNS   varchar(250)   
 
 as  
begin  
    declare @strinvoice    nvarchar(250)
    declare @invoiceno    nvarchar(50) --发票编号

    set @strinvoice = '' 
    
    declare cur cursor for 
    select invoiceno from tblBizReceiptBillDetail where ReceiptNO = @no order by InvoiceNO

    open cur
    fetch next from cur INTO @invoiceno 
    while @@fetch_status = 0
    begin
        set @strinvoice = @strinvoice + ',' + @invoiceno
        FETCH NEXT FROM cur INTO @invoiceno
    end
    close cur

    if(len(@strinvoice) > 0)
    begin
        set @strinvoice = substring(@strinvoice, 2, 250)
    end
    return   @strinvoice   
 
END

相同条件 多行变一行，不知道是不是你要的
declare @tb table (id int, value varchar(10)) 
insert into @tb values(1, 'aa') 
insert into @tb values(1, 'bb') 
insert into @tb values(2, 'aaa') 
insert into @tb values(2, 'bbb') 
insert into @tb values(2, 'ccc')

select id , [ccname]=
stuff((select ' '+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '') 
from @tb as tv
group by id 
/*
id    ccname
1    aa bb
2    aaa bbb ccc*/

------解决方案--------------------


硬扯上的没啥用啊
这做事方式太敷衍了  和老板沟通 用其他方式
解决一时 又解决不了 以后的问题

------解决方案--------------------
探讨

引用:

引用:

SQL code

相同条件 多行变一行，不知道是不是你要的




太感谢了搞定了大部分页面了
最后 一个页面
里面掉用了三个标量值函数
SQL code

[dbo].[getDeclarationInvoice](a.DeclarationNO) As InvoiceNOs,
……


把老板开了