SQL loop problem

Question

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?

Sounds like a pivot query, but it'd be nice to have a sample of data and an expected output based on that data. How do you know which address is first/second/third/etc and what database is this for? — OMG Ponies
– OMG Ponies, Commented Jul 19, 2010 at 2:18
If you post code, XML, or fixed table structures, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! No need for messy markup stuff..... — marc_s
– marc_s, Commented Jul 19, 2010 at 5:07

Brian Webster · Accepted Answer · 2010-07-19 02:12:52Z

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

edited Jul 19, 2010 at 2:12

answered Jul 19, 2010 at 2:04

Brian Webster

31k51 gold badges158 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL loop problem

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related