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

James Aylard evolt at pixelwright.com
Mon Mar 3 15:53:02 CST 2003


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.
    This would seem to require some sort of looping, which isn't a
particularly robust feature in Transact-SQL, and one which I have not used
before in a stored procedure.
    Can anyone suggest a reasonably straightforward approach? I will try to
clarify my examples if they are too muddy. Please understand that I have no
control over the design of Table B and must produce output to fit its
format.
    Thanks.

James Aylard
jaylard at members.evolt.org




More information about the thelist mailing list