SQL loop problem

Question

Hi guys, I have a problem, I hope somebody out there can help. I'm not really good at sql programming so I need help from u guys.

Here's my problem, I have customercode that has possible of 2 or 3 addresses. sample:

cust1  address1
cust1  address2
cust1  address3
cust2  address1
cust2  address2

I want to generate a report in .net to be like this:

cust1  address1  address2  address3
cust2  address1  address2

Can somebody help?

Answer 1

This assumes 2 tables:

 select 
    custName, A1.Address + ' ' + A2.Address + ' ' + A3.Address
 from
    Customers as C
    inner join Address as A1 on A1.CustKey = C.CustKey
    inner join Address as A2 on A2.CustKey = C.CustKey and A1.AddressKey <> A2.Addresskey
    left join Address as A3 on A3.CustKey = C.CustKey and A3.AddressKey <> A1.AddressKey and A3.AddressKey <> A2.AddressKey

Edit to show:

This assumes 1 table:

 select 
    A1.Customer, 
    A1.Address + ' ' + A2.Address + ' ' + A3.Address
 from
    Customer as A1 
    inner join Customer as A2 on A2.CustKey = A.CustKey and A1.Address <> A2.Address 
    left join Customer as A3 on A3.CustKey = A.CustKey and A3.Address <> A1.Address and A3.Address <> A2.Address 

Answer 2

here's the very detail prob: This belongs to only one table

custcode      address

cust1            capitol, cebu city

cust1            gen. maxilom, cebu city

cust1            guadalupe, cebu city

cust2            paknaan, mandaue city

cust2            basak, mandaue city

cust3            lapu-lapu city

in my report i want to have this fields

customer name      location1                  location2                            location3

cust1                      capitol, cebu city      gen. maxilom, cebu city      guadalupe, cebu city

cust2                      paknaan, mandaue     basak, mandaue             lapu-lapu city

please help..