Click here to Skip to main content
15,566,394 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.

SQL
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

</blockquote>


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.

Quote:
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
Posted
Updated 13-Jan-15 19:53pm
v3
Comments
Sinisa Hajnal 14-Jan-15 3:34am    
Obviously, remove nInstallmentNo from the query :)
kalsa 14-Jan-15 3:48am    
I removed it but not working. Can u tell me query that get my results ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900