I've been tasked with creating a table that contains a customer# and all doctors within certain proximity of that customer in ONE row. I'll explain. one customer may have more than one Dr. avail within proximity, ALSO one Dr. may have more than one office within proximity of that one customer, so the data in the table would look like this:
Cust# DocId DocName DocProfile DocCity DocState
111 aaa Dr.John Doe Our prac... Sunrise FL
111 aaa Dr.John Doe Our prac... Deerfield FL
111 bbb Dr.Jane Doe It's with... Tamarac FL
222 ccc Dr.Bill Doe We've.... Encinitas CA
222 ccc Dr.Bill Doe We've.... Encinitas CA(2 offices in same city)
222 ddd Dr.Ray Doe I'm glad... Sherman CA
222 ddd Dr.Ray Doe I'm glad... Agoura CA
222 eee Dr.Ruth Doe We're loc.. Studio CA
222 fff Dr.Brian Doe We strive... Palm Sp CA
My table right now follows the structure and data above. My next step is to create a table to have a single row for each cust# with the doctor(s) column(s) concatenated into one column. i.e.
CustomerNo Doctors in Area
111 John Doe Our prac… Sunrise FL; John Doe Our prac… Deerfield FL; Jane Doe It's with… Tamarac FL
This is were I'm stuck. what's the optimal way to go about this? ROWNUMBER OVER? WHILE statement? IF THEN statement? not really sure. Any light shed is appreciated.