Normalizing data using Unpivot or Cross Apply in SQL Server 2008

Question

I'm having trouble unpivoting some data. I see other examples on here for unpivoting tables but I have pairs which need unpivoted. There are actually 10+ disease/date pairs in my dataset, but I've chosen three pairs to simplify the example.

Here's my source data:

MRN      GUMCID     Event    BreastID  BreastDate  ColonID ColonDate    PancID  PancDate
2000012  90000001   event_1  42        1/1/2000    43      8/5/2014     44      8/6/2012
2008006  90000020   event_1  102       5/7/2003    103     9/11/2012        
2000012  90002002   event_1  900       8/5/2004    902     12/5/2009
2000012  90002002   event_2  1000      9/6/2006             

Here's what I need it converted to:

MRN     GUMCID    Event     TissueType  Date        ID
2000012 90000001  event_1   BreastID    1/1/2000    42
2000012 90000001  event_1   ColonID     8/5/2014    43
2000012 90000001  event_1   PancID      8/6/2012    44
2008006 90000020  event_1   BreastID    5/7/2003    102
2008006 90000020  event_1   ColonID     9/11/2012   103
2000012 90002002  event_1   BreastID    8/5/2004    900
2000012 90002002  event_1   PancID      12/5/2009   902
2000012 90002002  event_2   BreastID    9/6/2006    1000

I've fumbled around with unpivot and cross apply and can't seem to make this work. Any thoughts are appreciated!

Answer 1

select mrd, gumcid, event, tissuetype, date, id
from tbl
cross apply (
    values 
        ('breastid', breastid, breastdate),
        ('colonid', colonid, colondate),
        ('pancid', pancid, pancdate)
) t(tissuetype, id, date)
where id is not null