Luis Cazares (8/27/2012)
I'm sorry Matt, but your query looks complicated and more expensive than a simple Cross Tab that would require only 2 tables and an inner join.I have the query but I don't want to post it so the OP can practice.
I don't know, I managed to quickly come up with the same query Matt did, and I am trying to figure out how you can do it with only 2 of the 3 tables.
Started with this to get the data needed for the pivot:
select
t1.conid,
t1.location,
t3.certdesc + 'date' as certname,
t2.certdate
from
table1 t1
cross join table3 t3
left outer join table2 t2
on t2.cert = t3.certdesc and
t1.conid = t2.conid