How can I select only the first record with each foreign key in SQL Server?

Question

I have rows in a contact details database that look like this:

contactID - bioID - AddressLine1 - City 
393         1       1 nowhere st   toronto
3921        1       2 somewhere st vancouver
3231        2       1 anywhere rd  barrie
1122        2       2 overthere st halifax      

I am currently inner joining this to a bio table with columns firstname, lastname etc and the results look like this:

bioid    firstname    lastname    addressline1    city
1        some         guy         1 nowhere st    toronto
1        some         guy         2 somewhere st  vancouver
2        that         girl        1 anywhere rd   barrie
2        that         girl        2 overthere st  halifax

So I am basically getting 2 rows for every bio. Is there anyway I can select this all as one row like this:

bioid    firstname    lastname   addressline1x1   cityx1    addressline1x2    cityx2
1        some         guy        1 nowhere st     toronto   2 somewhere st    vancouver
2        that         girl       1 anywhere rd    barrie    2 overthere st    halifax

Any help is appreciated.

Thanks, Thomas

EDIT:

Thanks a lot to Denis and Justin I was able to solve this problem. However, now I have another.

I would like to actually select the addresses as 1 field such as:

bioid    firstname    lastname    primary address         secondary address
1        some         guy         1 nowhere st, toronto   2 somewhere st, vanvouver
2        that         girl        1 anywhere rd, barrie   2 overthere st, halifax

I know I can do it by concatenating columns like:

cd1.addressline1 + ', ' cd1.city AS 'Primary Address' 

However, some of the fields are empty in the records, for example if there is no secondary address - so how can I make it so the secondary address doesn't output ', '? The fields are empty strings, not NULL values.

Thanks again!

EDIT: I've got this working using:

STUFF(COALESCE(', ' + NULLIF(C1.[AddressLine1], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[AddressLine2], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[City], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[State], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[Country], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[ZipCode], ''), ''),1, 1, '') AS 'Primary Address'

Answer 1

You could try a PIVOT TABLE or a RECURSIVE CTE. The Pivot table would work if you know how many addresses the user will have (however, if you know that you could probably hard-code it too :)), the CTE will work for an unknown number of addresses, however, it would not create multiple columns, but instead a comma separated list.

Either way you go, you will probably need to utilize ROW_NUMBER to create the uniqueness order.

Here is what should be a working example of the Recursive CTE:

SELECT *, ROW_NUMBER() OVER(PARTITION BY bioid ORDER BY contactid) AS rownum
INTO #TableToRecurse
FROMContactDetails

;WITH FinalOutput AS
(
--Anchor row of recursion
SELECT bioid, firstname, lastname, addressline, city, rownum
FROM #TableToRecurse 
WHERE rownum = 1
UNION ALL 
--Recursion piece
SELECT tr.bioid, tr.firstname, tr.lastname, 
    FinalOutput.addressline + ', ' + tr.addressline, FinalOutput.city + ', ' + tr.city, tr.rownum
FROM #TableToRecurse AS tr
    JOIN FinalOutput
        ON  FinalOutput.bioid = tr.bioid AND FinalOutput.rownum = tr.rownum +1
)
--Final output only showing the last row (Max)
--Which should have everything concatenated together
SELECT FinalOutput.bioid, FinalOutput.firstname, FinalOutput.lastname, 
    FinalOutput.addressline, FinalOutput.city
FROM FinalOutput 
    JOIN 
    (
        SELECT MAX(rownum) AS MaxRowNum, bioid
        FROM FinalOutput
        GROUP BY bioid
    ) AS MaxForEach
        ON FinalOutput.bioid = MaxForEach.bioid 
            AND FinalOutput.rownum = MaxForEach.MaxRowNum

Answer 2

;with cd as(
select *, rn = dense_rank(partition by bioid order by addressline, city)
from ContactDetails cd
)
select c.bioid, c.firstname, c.lastname, cd1.AddressLine, cd1.City, cd2.AddressLine, cd2.City, cd3.AddressLine, cd3.City
from Contact c 
left join cd cd1 on c.bioid = cd1.bioid and cd1.rn = 1
left join cd cd2 on c.bioid = cd2.bioid and cd2.rn = 2
left join cd cd3 on c.bioid = cd3.bioid and cd3.rn = 3