/*------------------------------------------------------------------*/
/*Pivot multiple rows into a single row with multiple columns across*/
/*------------------------------------------------------------------*/
/*-----------------------------------------------------*/
/* technique 1 -                                       */
/*      for MySql  use MAX( IF(.......) ) and GROUP BY */
/*      for Oracle use MAX( DECODE(...) ) and GROUP BY */
/*      for all    use MAX( CASE ...... ) and GROUP BY */
/*-----------------------------------------------------*/

/* in MySql - we can use the IF( ) function */

SELECT  student_id, fname, lname, 
        MAX( IF(course_id='X52-9740','X',null) ) as HTML,
        MAX( IF(course_id='X52-9272','X',null) ) as SEQL,
        MAX( IF(course_id='X52-9759','X',null) ) as XML,
        MAX( IF(course_id='X52-9755','X',null) ) as JScript,
        MAX( IF(course_id='X52-9238','X',null) ) as Java
   FROM student LEFT JOIN class
     ON ssn=stu_ssn
  GROUP BY student_id, fname, lname
  ORDER BY 1;

/* in Oracle - we can use the DECODE() function */

SELECT  student_id, fname, lname, 
        MAX( DECODE(course_id,'X52-9740','X') ) as HTML,
        MAX( DECODE(course_id,'X52-9272','X') ) as SEQL,
        MAX( DECODE(course_id,'X52-9759','X') ) as XML,
        MAX( DECODE(course_id,'X52-9755','X') ) as JScript,
        MAX( DECODE(course_id,'X52-9238','X') ) as Java
   FROM student LEFT JOIN class
     ON ssn=stu_ssn
  GROUP BY student_id, fname, lname
  ORDER BY 1;
  
/* Below we use CASE, which will work for all databases  */

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

  
/*-----------------------------------------------------*/
/* technique 2 - Using inline views                    */
/*-----------------------------------------------------*/

SELECT student_id, fname, lname, HTML, SEQL, XML, JS as JScript, Java  
FROM student s 
LEFT JOIN (select stu_ssn, 'X' as HTML  from class 
            where course_id='X52-9740') h  ON ssn= h.stu_ssn
LEFT JOIN (select stu_ssn, 'X' as SEQL  from class 
            where course_id='X52-9272') s  ON ssn= s.stu_ssn
LEFT JOIN (select stu_ssn, 'X'as XML    from class 
            where course_id='X52-9759') x  ON ssn= x.stu_ssn
LEFT JOIN (select stu_ssn, 'X' as JS    from class 
            where course_id='X52-9755') js ON ssn= js.stu_ssn
LEFT JOIN (select stu_ssn, 'X' as JAVA  from class 
            where course_id='X52-9238') jv ON ssn= jv.stu_ssn
ORDER BY 1;


/* ---------------------------------------------------------------*/
/* technique 3 - joining to multiple instances of the same table  */ 
/*               each time only selecting a particular class      */
/* ---------------------------------------------------------------*/

SELECT  student_id, fname, lname, h.course_id as HTML, 
      CASE WHEN s.course_id >' ' THEN 'X' END as SEQL, 
      CASE WHEN x.course_id >' ' THEN 'X' END as XML, 
      CASE WHEN j.course_id >' ' THEN 'X' END as JCRIPT,
      CASE WHEN v.course_id >' ' THEN 'X' END as JAVA
FROM student
LEFT JOIN class h  ON ssn=h.stu_ssn AND h.course_id='X52-9740' 
LEFT JOIN class s  ON ssn=s.stu_ssn AND s.course_id='X52-9272' 
LEFT JOIN class x  ON ssn=x.stu_ssn AND x.course_id='X52-9759' 
LEFT JOIN class j  ON ssn=j.stu_ssn AND j.course_id='X52-9755' 
LEFT JOIN class v  ON ssn=v.stu_ssn AND v.course_id='X52-9238' 
ORDER BY 1;


/* ---------------------------------------------------------------*/
/* technique 4 - Using subquery on the select as a derived column */
/* this is costly and should only be used as last resort          */
/* ---------------------------------------------------------------*/

SELECT student_id, fname, lname,
   (SELECT 'X' FROM class c WHERE ssn=stu_ssn and course_id='X52-9740') as HTML, 
   (SELECT 'X' FROM class c WHERE ssn=stu_ssn and course_id='X52-9272') as SEQL, 
   (SELECT 'X' FROM class c WHERE ssn=stu_ssn and course_id='X52-9759') as XML,
   (SELECT 'X' FROM class c WHERE ssn=stu_ssn and course_id='X52-9755') as JS, 
   (SELECT 'X' FROM class c WHERE ssn=stu_ssn and course_id='X52-9238') as Java 
FROM student s
ORDER BY 1;


/*---------------------------------------------------------*/
/* technique 1, another example                            */
/*---------------------------------------------------------*/

SELECT lname, fname, MAX( CASE addr_type WHEN 'H' THEN city END) AS "home city", 
                     MAX( CASE addr_type WHEN 'W' THEN city END) AS "work city"
FROM student  left join  address  on student_id = stu_id
GROUP BY lname, fname
ORDER BY lname, fname;


/*---------------------------------------------------------*/
/* technique 3, another example                            */
/*---------------------------------------------------------*/

SELECT lname, fname,  h.city AS "home city",  w.city AS "work city"
FROM student s left join address h on s.student_id=h.stu_id and h.addr_type='H'
               left join address w on s.student_id=w.stu_id and w.addr_type='W'
ORDER BY lname, fname;