Hi ,
please tell me How to separate Multiple InvoiceNos of one ReceiptID by comma in storedprocedure.
I have Store procedure as like :-



USE [TestExample]
GO
ALTER Procedure [TestExample].[RptCashChequeReceiptVoucherHeaderSingle]

 @CompanyID NVARCHAR(36),
 @DivisionID NVARCHAR(36),
 @DepartmentID NVARCHAR(36),
 @ReceiptID NVARCHAR(36)

 AS


declare @CompanyCurrencySymb nvarchar(1)
declare @DHCurrencySymb nvarchar(1)
set @DHCurrencySymb=(select CurrenycySymbol from CurrencyTypes where
CompanyID =@CompanyID
AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID
and CurrencyId=(select CurrencyId from CurrencyTypes
where CompanyID =@CompanyID AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID and CurrencyId='DH'))

set @CompanyCurrencySymb=(select CurrenycySymbol from CurrencyTypes where
CompanyID =@CompanyID
AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID
and CurrencyId=(select CurrencyId from Companies
where CompanyID =@CompanyID AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID))

SELECT Distinct ReceiptsHeader.ReceiptID,
CASE
    WHEN ReceiptsHeader.ReceiptTypeID = 'Check' THEN
    ReceiptsHeader.CheckNumber
    ELSE
    'Cash'
END as ByCashOrChequeNo,
        ReceiptsHeader.Amount,
        ReceiptsHeader.ReceiptTypeID,
        ReceiptsHeader.CheckNumber,
        ReceiptsHeader.TransactionDate as ReceiptDate,
        CustomerInformation.CustomerName,
        ReceiptsDetail.DocumentNumber as InvoiceNumber,
        @CompanyCurrencySymb as CompanyCurrencySymb ,
        @DHCurrencySymb as DHCurrencySymb
FROM ReceiptsHeader Left OUTER JOIN ReceiptsDetail
ON ReceiptsHeader.CompanyID = ReceiptsDetail.CompanyID AND ReceiptsHeader.DivisionID = ReceiptsDetail.DivisionID
        AND ReceiptsHeader.DepartmentID =ReceiptsDetail.DepartmentID and ReceiptsHeader.ReceiptID = ReceiptsDetail.ReceiptID
        Left OUTER JOIN CustomerInformation ON ReceiptsHeader.CompanyID = CustomerInformation.CompanyID
        AND ReceiptsHeader.DivisionID = CustomerInformation.DivisionID AND ReceiptsHeader.DepartmentID = CustomerInformation.DepartmentID
        AND ReceiptsHeader.CustomerID = CustomerInformation.CustomerID
WHERE ReceiptsHeader.CompanyID = @CompanyID
        AND ReceiptsHeader.DivisionID = @DivisionID
        AND ReceiptsHeader.DepartmentID = @DepartmentID
        AND ReceiptsHeader.ReceiptID = @ReceiptID


O/p of this StoredProcedure is as:

AssetID AssetTypeID Cost Additions Disposal subtotal, Depreciation, NetBookValue GlTransactionDate
a001 Building Improvements 10000.00 4000.00 2000.00 12000.00 8000.00 4000.00 2007-10-12 00:00:00.000
a003 Residence Furn/Equipment 30000.00 5000.00 4000.00 31000.00 7000.00 24000.00 2007-10-08 14:18:36.000


But when i generate report for one ReceiptID having Multiple InvoiceNos, this code generate report for each invoiceNo. seperately.
I want one Receipt report having one receiptID and multiple invoice nos separated by comma(,).

Rg2005