Question:
I’ve got a table structure that looks like the following:Customer TableCUST_IDNAMECODE_LISTCode TableCUST_IDCODE The code table can have any number of codes for each customer. I’m trying to find a SQL statement that can concatenate the list of CODE rows and fill in that CODE_LIST in the Customer Table. Right now I’ve got a VB program that does it using an array implementation, but I’d like to be able to do it without having to recompile the code every time the requirements change.
Answer:
As a Q&D solution to this, and banking on the idea that the codes in the list are something on the order of a char(3) lets say (to be optimistic), and the number of records is also something on the order of lets say 15 code rows per customer [eg, not to exceed the limit of whatever you declare the variable for], I’d hazzard that you’d be safe enough doing:
create a procedure that declared a variable of varchar(255) @StuffMyList, declare a cursor on the code tableopen it , for each customer id for each code in that customer id add the new code to the end of @StuffMyList look for next code close code loopclose customer loopdeallocate