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.