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

James Aylard evolt at pixelwright.com
Mon Mar 3 16:34:01 CST 2003

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

More information about the thelist mailing list