Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenating several row columns into one Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 2:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 206, Visits: 449
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




Post #1500927
Posted Wednesday, October 2, 2013 3:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Check the following article. It explains the method you might need.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1500940
Posted Monday, October 7, 2013 7:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 206, Visits: 449
Thanks for the link, Luis. That worked.

J.
Post #1502145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse