Duplicates and Joins

1

Hello.

I can't seem to get this right. Here is my SQL Statement:

Select Distinct
ln.MemberNumber,
ln.LoanNumber,
ln.OpenDate,
ln.OriginalAmount,
ln.Balance,
ln.Closed,
VehicleVIN=Case When lp.ItemName='DLVIN' Then lp.LoanPropertyValue else '0' End,
LoansPQ_Application=Case When lp.ItemName='OXCUDLAPP' Then lp.LoanPropertyValue Else '0' End,
LoanProduct= Case When ln.LoanType In ('7','27') Then 'Indirect Auto - Arizona'
When ln.LoanType In ('42','43') Then 'Indirect Auto - Texas'
When ln.LoanType in ('1','4') Then 'Direct Auto - Arizona'
When ln.LoanType in ('45','47') Then 'Direct Auto - Texas'
Else '0'
End,
Dealership_Code=Case When lp.ItemName='DLDEALER' Then lp.LoanPropertyValue else 'None Listed' End,
dl.DealerName

From
LoanProperty lp
Join Loan ln
On ln.MembershipKey=lp.MembershipKey AND ln.LoanNumber=lp.LoanNumber
Left Join Dealerships dl
On dl.DealerCode=lp.LoanPropertyValue

Where
ln.OpenDate Between '03/01/2018' AND '02/28/2019'
AND lp.MemberNumber = '1007810'
AND lp.LoanNumber='144'
AND lp.ItemName IN ('DLDEALER','DLVIN','OXCUDLAPP')

And here is the result. How do I collapse this into one row with all of the values?

MemberNumber LoanNumber OpenDate OriginalAmount Balance Closed VehicleVIN LoansPQ_Application LoanProduct Dealership_Code DealerName
XXX7810 144 2018-03-19 13281.05 10809.49 0 0 0 Indirect Auto - XXXXXXX OK SUBARU SUPERSTORE OF XXXXXXXX
XXX7810 144 2018-03-19 13281.05 10809.49 0 0 325058 Indirect Auto - XXXXXXX None Listed NULL
XXX7810 144 2018-03-19 13281.05 10809.49 0 JFXXTAMXXJ82958XX 0 Indirect Auto - XXXXXXX None Listed NULL

Thanks.

2

Depends on what you mean by "collapse"
You have shown us what you are getting, can you post what you expect to see

3
What I see
MemberNumber LoanNumber OpenDate OriginalAmount Balance Closed VehicleVIN LoansPQ_Application LoanProduct Dealership_Code DealerName
1XX78X0 144 3/19/2018 13281.05 10809.49 0 0 0 Indirect Auto - XXXXXXX OK SUBARU SUPERSTORE
1XX78X0 144 3/19/2018 13281.05 10809.49 0 0 325058 Indirect Auto - XXXXXXX None Listed NULL
1XX78X0 144 3/19/2018 13281.05 10809.49 0 JFXGTAMCXJ8295X65 0 Indirect Auto - XXXXXXX None Listed NULL
What I want to See
MemberNumber LoanNumber OpenDate OriginalAmount Balance Closed VehicleVIN LoansPQ_Application LoanProduct Dealership_Code DealerName
1XX78X0 144 3/19/2018 13281.05 10809.49 0 JFXGTAMCXJ8295X65 325058 Indirect Auto - XXXXXXX None Listed SUBARU SUPERSTORE
4

Would a sub query do the trick here to get my desired result?

5

I would suggest using GROUP BY to get the desired results - but you have 'None Listed' for the dealership code. How do you determine that to be the desired result?

For the other columns - you can use MAX which will be greater than 0 or NULL...but I would think OK would be the expected/desired result for that column and not 'None Listed'.