/*-------------------------------------------------*/
/* Pivoting Rows into Columns - CASE and then MAX  */
/*-------------------------------------------------*/

/* Get all the students and their courses */

SELECT  lname, fname, course_id 
  FROM  student, class
 WHERE  ssn=stu_ssn
 ORDER BY 1;


/* Now let's spread the courses into multiple columns */

SELECT  lname, fname, 
        CASE WHEN course_id='X52-9759' THEN course_id END as "XML",
        CASE WHEN course_id='X52-9740' THEN course_id END as "HTML",
        CASE WHEN course_id='X52-9272' THEN course_id END as "SQL"
   FROM  student, class
 WHERE  ssn=stu_ssn
 ORDER  BY 1; 
 
/* Now let's GROUP BY the student lname and fname */
/* and aggregate the rest of the columns          */

SELECT  lname, fname, 
        MAX(CASE WHEN course_id='X52-9759' THEN course_id END) as "XML",
        MAX(CASE WHEN course_id='X52-9740' THEN course_id END) as "HTML",
        MAX(CASE WHEN course_id='X52-9272' THEN course_id END) as "SQL"
  FROM  student, class
 WHERE  ssn=stu_ssn
 GROUP  BY lname, fname
 ORDER  BY 1; 


/* Now let's substitute an 'X' for curse_id  */
/* and get all students, using an outer join */ 

SELECT  lname, fname, 
        MAX(CASE WHEN course_id='X52-9759' THEN 'X' END) as "XML",
        MAX(CASE WHEN course_id='X52-9740' THEN 'X' END) as "HTML",
        MAX(CASE WHEN course_id='X52-9272' THEN 'X' END) as "SQL"
  FROM  student LEFT JOIN class
    ON  ssn=stu_ssn
 GROUP  BY lname, fname
 ORDER  BY 1;