Home Forums SQL Server 2008 T-SQL (SS2K8) does not work with outer join or outer apply RE: does not work with outer join or outer apply

  • August 28, 2012 at 8:04 am

    #1530830

    Luis Cazares (8/28/2012)

    I'm sorry I didn't replied yesterday but we had to leave the office because of Isaac.

    Here's a query that will work as a static report. It can be easily transformed to a dynamic query to allow more certifications.

    SELECT location,

    MAX( CASE WHEN cert = 'cert1' THEN certdate END) AS cert1,

    MAX( CASE WHEN cert = 'cert2' THEN certdate END) AS cert2,

    MAX( CASE WHEN cert = 'cert3' THEN certdate END) AS cert3,

    MAX( CASE WHEN cert = 'cert4' THEN certdate END) AS cert4

    FROM#table1 t1

    JOIN#table2 t2 ON t1.conid = t2.conid

    GROUP BY t1.location

    PS. I can't stop laughing at all the nonesense from the previous poster. Certificates with a location column? Seriously?

    Okay, I see now that I picked up the saw. If you make the change to the tables that Sean suggested, then you need all three tables. I'm glad I got a good nights sleep last night.