I'm writing a SQL Server 2008 report that will compare genetic test results for animals. A genetic test consists of an animalId, a gene and a result. Not all animals will have the same genes tested but I need to be able to display the results side-by-side for a given set of animals and only include the genes that are present for at least one of the selected animals.

My TestResult table has the following data in it:

animalId    gene    result
1           a       CC
1           b       CT
1           d       TT
2           a       CT
2           b       CT
2           c       TT
3           a       CT
3           b       TT
3           c       CC
3           d       CC
3           e       TT

I need to generate a result set that looks like the following. Note that Animal 3 is not being displayed (user doesn't want to see its results) and neither are results for Gene "e" since neither Animal 1 nor Animal 2 have a result for that gene:

SireID  SireResult  CalfID  CalfResult Gene
1       CC          2       CT          a
1       CT          2       CT          b
1       NULL        2       TT          c
1       TT          2       NULL        d

But I can only manage to get this:

SireID  SireResult  CalfID  CalfResult Gene
1       CC          2       CT          a
1       CT          2       CT          b
NULL    NULL        2       TT          c
1       TT          NULL    NULL        d

This is the query I'm using.

SELECT
    sire.animalId AS 'SireID'
    ,sire.result AS 'SireResult'
    ,calf.animalId AS 'CalfID'
    ,calf.result AS 'CalfResult'
    ,sire.gene AS 'Gene'
FROM
    (SELECT 
        s.animalId 
        ,s.result 
        ,m1.gene 
    FROM
        (SELECT [animalId ]
            ,result 
            ,gene 
        FROM TestResult
        WHERE animalId IN (1)) s
    FULL JOIN
        (SELECT DISTINCT 
            gene 
        FROM TestResult
        WHERE animalId IN (1, 2)) m1
    ON s.marker = m1.marker) sire
FULL JOIN
    (SELECT 
        c.animalId 
        ,c.result 
        ,m2.gene 
    FROM
        (SELECT animalId 
            ,result 
            ,gene 
        FROM TestResult
        WHERE animalId IN (2)) c
    FULL JOIN
        (SELECT DISTINCT 
            gene 
        FROM TestResult
        WHERE animalId IN (1, 2)) m2
    ON c.gene = m2.gene) calf
ON 
    sire.gene = calf.gene

How do I get the SireIDs and CalfIDs to display their values when they don't have a record associated with a particular Gene? I was thinking of using COALESCE but I can't figure out how to specify the correct animalId to pass in. Any help would be appreciated.