SQLTeam.com | Weblogs | Forums

Collapsing Rows to make a single Row


#1

Hello.

I am not sure how to explain this, but I am getting this:
|XXX04XX|125|28800.00|0.00|28800.00|0.999999999652777777898|0.0000|17.9000|
|XXX04XX|125|28800.00|0.00|28800.00|0.999999999652777777898|9.9000|0.0000|

What I am trying to get to is this:
|XXX04XX|125|28800.00|0.00|28800.00|0.999999999652777777898|9.9000|17.9000|

Here is my code:

Select Distinct
ln.MemberNumber,
ln.LoanNumber,
ln.Limit,
ln.Balance,
AvailabilityAmount=ln.limit-ln.Balance,
AvailabilityPercent=((ln.Limit+.00001)-(ln.Balance+.00001))/(ln.Limit+.00001),
PurchaseRate= Case When ccs.SR_BalanceTypeIndex='2' Then ccs.SubLOanRate
Else '0'
End,
CashAdvanceRate= Case When ccs.SR_BalanceTypeIndex='1' Then ccs.SubLoanRate
Else '0'
End

From
Loan Ln
Join CreditCardSplitRateDetail ccs
On ccs.MemberNumber=ln.MemberNumber AND ccs.LoanNumber=ln.LoanNumber

Where
ln.LoanType In ('34','55','69','89','108','97','99')
AND ln.Closed<>'-1'
AND ln.MemberNumber<>'XX001XX'
AND ((ln.Limit+.00001)-(ln.Balance+.00001))/(ln.Limit+.00001)>=.50
AND ccs.RateIndexRate='5.25'
AND ccs.SR_BalanceTypeIndex<>'0'


#2
Select 
ln.MemberNumber,
ln.LoanNumber,
ln.Limit,
ln.Balance,
AvailabilityAmount=ln.limit-ln.Balance,
AvailabilityPercent=((ln.Limit+.00001)-(ln.Balance+.00001))/(ln.Limit+.00001),
PurchaseRate= Sum(Case When ccs.SR_BalanceTypeIndex='2' Then ccs.SubLOanRate
Else '0'
End),
CashAdvanceRate= Sum(Case When ccs.SR_BalanceTypeIndex='1' Then ccs.SubLoanRate
Else '0'
End)
From
Loan Ln
Join CreditCardSplitRateDetail ccs
On ccs.MemberNumber=ln.MemberNumber AND ccs.LoanNumber=ln.LoanNumber
Group by ln.MemberNumber,
ln.LoanNumber,
ln.Limit,
ln.Balance,
ln.limit-ln.Balance,
((ln.Limit+.00001)-(ln.Balance+.00001))/(ln.Limit+.00001)

#3

Thank you!!! It seems so simple after I read your post!


#4

You're welcome, glad it helped!