Concatenating several row columns into one

  • 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.

    Regards,

    John

  • Check the following article. It explains the method you might need.

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the link, Luis. That worked.

    J.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply