SQL - Pull two records into one 1

[djwatts]

Hi,

I need to be able to pull data from two rows of the same table into one row i.e.

CONTRACT_NO;PERSON_NO
100;12345
100;12346
101;65432
101;65431

into

CONTRACT_NO;PERSON1_NO;PERSON2_NO
100;12345;12346
101;65432;65431

I know it goes against everything relational databases stand for but we are converting one system to another with an entirely different format. I could write a macro in Excel but can it be done with SQL?

Thanks for any help

Dan Watts

 

[Golom]

Something like

Select A.Contract, A.Person_No As Person1_No,
                   B.Person_No As Person2_No

From tbl As A INNER JOIN tbl As B
     ON A.Contract = B.Contract

Where A.Person_No < B.Person_No

The restriction is that there MUST be exactly two records for each contract and they must have different Person_No codes.

 

[djwatts]

I think that's along the right lines just needs a bit of fine tuning.

Thanks