Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jaylard
Starting Member
3 Posts |
 Posted - 2003-03-05 : 12:14:36
|
| I need to take data that currently exists in multiple rows of one table and merge it into a single row in another table. The structure is something like this:Table A======= Record_1 ID (non-unique) Field_x Field_y Field_z Record_2 ID Field_x Field_y Field_z[...]Table B======= Record_1 ID (unique) Field_x1 Field_y1 Field_z1 Field_x2 Field_y2 Field_z2If the ID matches between Table A and Table B, then I want to grab up to five records from Table A (although for simplicity my example suggests only two records, I actually want up to five), extract the values from each record's pertinent fields, and plug them into Table B. Table A may contain zero or more records that pertain to Table B. The records extracted from Table A need to be in descending order according to a percentage value in one of the fields so that only the records with the highest percentage values are selected. I want to do this for all records, not just a particular one.It may make more sense to construct an intermediary temporary table of the data extracted from Table A, and then update Table B from that intermediary table. That would be fine, too.Please understand that I have no control over the design of Table B and must produce output to fit its format.Can anyone help? Thanks. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-05 : 12:30:49
|
| if you have 5 records in table A with the same ID, where do you put the 3rd, 4th and 5th record in table B? Do you have a Field_x3, Field_x4 and Field_x5?Jay White{0} |
 |
|
|
jaylard
Starting Member
3 Posts |
Posted - 2003-03-05 : 12:36:15
|
Jay,quote:
if you have 5 records in table A with the same ID, where do you put the 3rd, 4th and 5th record in table B? Do you have a Field_x3, Field_x4 and Field_x5?
Yes, that is correct. For simplicity my example suggests selecting only two rows from Table A, but Table B is, in fact, designed to accommodate up to five, and that is what my query needs to accommodate, as well.Thanks. |
|
|
|
ojn.
Starting Member
10 Posts |
Posted - 2003-03-05 : 16:44:55
|
| You could take a look at RAC for this kind of xtab issue.---ojwww.rac4sql.net |
|
|
|
jaylard
Starting Member
3 Posts |
Posted - 2003-03-05 : 17:20:39
|
oj,quote:
You could take a look at RAC for this kind of xtab issue.
I need to accomplish this without the use of third-party tools, but I do appreciate the response. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
How Do I Combine Multiple Rows into One?