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

[Jason Handby]

> SQL Gurus,
>
>  I'm working on a project in Transact-SQL (in MS SQL Server 7) and 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_z2
>
>     If the ID matches between Table A and Table B, then I want to
> grab up to
> five records from Table A (I've suggested two as the max in my
> example, but
> five is the actual max) and plug them into Table B.

A couple of questions:

What's the primary key for table A?

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?



Jason