[thelist] SQL: How Do I Combine Multiple Rows into One?

[James Aylard]

Jason Handby wrote:

>> 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_z2
>>

> A couple of questions:
>
> What's the primary key for table A?

    Table A is actually a temporary table that has no primary key, although
the ID field is a unique alpha-numeric value.

> Does it matter what order the x y and z values get plugged into table
> B? If for example there are three matching records in table A, do you
> just want the first three sets of x,y,z fields in B to be populated,
> taking those records from table A in any order, and the remaining two
> sets of x,y,z fields to be left as NULL?

    The order does matter. One of the fields in Table A contains a
percentage value, which needs to be sorted in descending order prior to
populating Table B. If more than five records from table A match the ID in
Table B, those additional records would be ignored; if fewer than five
records from Table A match the ID in Table B, any non-populated fields in
Table B should be left null (which they are by default).

James Aylard
jaylard at members.evolt.org