Thursday, 8 August 2013

Displaying row as column in sqlserver

Declare @sqlstr varchar(4000)
Declare @FromDate varchar(4000),
@ToDate varchar(4000),
@FranchiseCode varchar(20)
set @sqlstr=''
set @FromDate='03/02/2013'
set @ToDate='06/03/2013'
set @FranchiseCode='HY'
set @sqlstr='select row_number() over (order by (SELECT (1))) AS SNo,
       convert(varchar(15),ReceiptDate,103) as ReceiptDate,
       ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Cash,
       ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cheque''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Cheque ,

       ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''CREDIT CARD''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as ''Credit_Card'',
     
       ISNULL((select SUM(Fine) from FeeReceipt where ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103)
       AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
       and ReceiptDate=FR.ReceiptDate),0) as Fine ,

        ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)+ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cheque''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)+ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''CREDIT CARD''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)
 as ''Total_Collection'' ,      

       ISNULL(CR.Amount,0) AS ''Chq_Return''
     
from FeeReceipt FR
left join
(select FranchiseCode,vDATE,ISNULL(SUM(AMOUNT),0) as "Amount" FROM CHEQUERETURN
WHERE vDATE between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
group by vDATE,FranchiseCode) CR
ON FR.ReceiptDate=CR.vDATE
AND FR.FranchiseCode=CR.FranchiseCode
WHERE
FR.ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
AND FR.FranchiseCode='''+@FranchiseCode+'''group by FR.ReceiptDate,CR.Amount,CR.FranchiseCode'


exec(@sqlstr) 

No comments:

Post a Comment