SQL Server Performance Forum – Threads Archive
turning rows into 1 field
Hello all, i am tasked with taking row data and packing it into one field. So say for example i have a client table with a foreign key to a phone number history table that tracks multiple phone numbers for the 1 client. I am doing data conversion and i have to take that 1 client row and put every single phone number the client has ever had into 1 field separated by comma’s. So what would have been:name DOB FK phone number ID
Frank Spence January 13, 1970 5678 Phone number ID Phone number
5678 111-1111
5678 222-2222
5678 333-3333 would become: name DOB phone number
Frank Spence January 13, 1970 111-1111, 222-2222, 333-3333 some people have 1 phone number others have 12 and im having a hard time coming up with a good way to do this. My beginning though was to make 12 functions(thats the most phone numbers to a client) that use 2 selects with order desc and the top function to one by one pull them out of the list backwards. Its very cumbersome and i was just putting it out there to see if there is a better way. Thank you in advance
Check if this post helps:http://forums.asp.net/thread/1687553.aspx ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
thank you for the link, ive read it over a few times but dont know exactly how it works or how i could apply it to my situation. That persons problem is the exact same as mine. Ill have to look into what a curser is.
Create a function that takes in Phone number ID as the parameter and returrns a list of phune numbers. call the function from your SELECT statement. a CURSOR will be much much expensive. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
Where do you want to show data?
If you use front end application, you can easily concatenate values from different rows Madhivanan Failing to plan is Planning to fail
]]>