TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
kalu singh rao
288
6.4k
109.3k
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
Reply
Answers (
0
)
Changing values with another value
inserting one table data to other table already have