Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 16, 1999

Condensing rows

Question:
I've got a table structure that looks like the following: Customer Table CUST_ID NAME CODE_LIST Code Table CUST_ID CODE 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 table
open 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 loop
close customer loop
deallocate 
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date