update one row with multiple values

[jeffwest1]

I may have missed it, or i am just being stupid.

I have a crystal report that I want to add some data to via SQL, everytime I run some code I get more than one result per person, this is correct as that is the way the data is layered, poor db design, but as i didn't build it i cant be to blame.

What I want is one row per person, so the many values they have are listed so, value1, value2,value3 etc etc

Problems I seem to have

I can get the main data ie client no,forename,surname etc, but the multiple values are defeating me.

I have a stored proc that pulls out first the basic details, then is supposed to pull out all the values and update the data in my table with the extra values, however, it only ever pulls out the first.

Without doing multiple cursors, or having to count the number of values and do a "Do while count >'0'" how do I populate this table.

I am resonably ok with SQL and Crystal, but this is just getting to me now.

 

[PWise]

Something like this

Select table1.field1,table1.field2 ,table2.field1,table2.field2,table3.field1,table3.field2
from table1
inner or left join table2
on table1.fieldname=table2.fieldname
inner or left join table3
on table1.fieldname=table3.fieldname

 

[jeffwest1]

Thats kind of what i can do already, what i need is for it to show across.


434632 Marcus Knight AT
434632 Marcus Knight DU

this shown as

434632 Marcus Knight AT,DU

 

[jeffwest1]

Ok, after adapting a few bits of code i found on here i now have the answer I want.

Trust me, you don't want to see the code, it is very messy.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.