kalu singh rao

kalu singh rao

  • 264
  • 6.4k
  • 106.1k

Get Data in A Single Row in SQL

Jan 14 2015 1:08 AM
I am using pivot table for getting data in a single row but it is not working. Where is mistakes ? Help me.

my Query is here.

Declare @Processvals Varchar(max)='1'
Select * from (
Select CM.ColCentre SHGName,ISNULL(SM.nTotalLoanAmt,0) LoanAmount,DD.dDate,DD.nEMI,dd.nInstallmentNo,ISNULL(SUM(TM.AvailBal),0) CBal,
CASe SM.nTotInstNo-DD.nInstallmentNo when 0 then 'LAST EMI' when 1 then 'SECOND LAST EMI' ELSE '' END EMISTR<blockquote class="quote"><div class="op">Quote:</div>,
CASe SM.nTotInstNo-DD.nInstallmentNo when 0 then 'LAST Date' when 1 then 'SECOND LAST Date' ELSE '' END DateSTR
from DepositDemand DD inner join Customers Cm on CM.nAciD = DD.nLoanAcID inner join
(Select items from Split(@Processvals,',')) SHG on SHG.items=CM.nColgroup inner join
SHGMember SM on SM.nMemberID = CM.nMemberID inner join View_AvailBalance TM on TM.nAcID = CM.nAcID where CM.EntType = 112 and DD.ddate <> '01-Jan-1900'
and dd.nInstallmentNo in (SM.nTotInstNo,SM.nTotInstNo-1)
group by Cm.nColGroup,CM.ColCentre,SM.nTotalLoanAmt,dd.nInstallmentNo,DD.dDate,DD.nEMI,DD.nLoanDeposit,SM.nTotInstNo
)p
PIVOT(
sum(nEMI) for emisTR in ([LAST EMI],[SECOND LAST EMI])
)PTBL
pivot(
min(dDate) for DateSTR in ([LAST Date],[SECOND LAST Date])
)PBL



i get this result:
SHGName LoanAmount nInstallmentNo CBal LAST EMI SECOND LAST EMI LAST Date SECOND LAST Date
GURMIT SHG 02 CHANNU 150000 12 72000 NULL 1400.00 NULL 2015-07-06 00:00:00.000
GURMIT SHG 02 CHANNU 150000 13 72000 767.00 NULL 2015-08-03 00:00:00.000 NULL

but i want to get this result as below.

SHGName LoanAmount CBal LAST EMI SECOND LAST EMI LAST Date SECOND LAST Date
GURMIT SHG 02 CHANNU 150000 72000 767.00 1400.00 2015-08-03 2015-07-06