/******************************************************************************
  Flatten out the student courses from vertical to horizontal
******************************************************************************/
drop procedure flatten
/
create procedure flatten()
begin
    ----------------------------------------------------------------------
    -- Step1: Create a report table with dynamic number of columns 
    --        For every unique value of the course_ids
    --        Create a string = 'course_id  varchar(25),'
    --        Create the report table
    ----------------------------------------------------------------------
    begin                               
        declare more        boolean default true;           
        declare v_course_id varchar(25);
        declare cur1 cursor for
            select distinct course_id from course;
        declare continue handler for not found set more=false;
        set @cols = '';
        open cur1;
    L1: while (true) do 
            fetch cur1 into v_course_id;
            if more=false then
                leave L1;
            end if;
            set v_course_id = replace(v_course_id,'-','_');  --dash not allowed
            set @cols = concat(@cols,v_course_id,' varchar(25), ');
        end while;
        set @cols = left(@cols, length(@cols)-2);       --get rid of last comma
        close cur1;
        set @stmt = concat('create temporary table report ( ',
                           'Name varchar(100) not null,     ',
                            @cols, ' )' );                      
        prepare statement from @stmt;
        execute statement;
        deallocate prepare statement;
    end;

    ----------------------------------------------------------------------
    -- Step2: Insert/Update into the report table
    --        Query the source_table, order by first column
    --        If name (col1) is not = prev_name, insert a new row
    --        For every course for the same name, update row with X
    ----------------------------------------------------------------------
    begin 
        declare more boolean default true;
        declare v_name      varchar(100);
        declare v_prev_name varchar(100) default '';
        declare v_course_id varchar(25);
        declare cur2 cursor for
            select concat(fname,' ',lname), course_id 
              from student join class on ssn = stu_ssn; 
        declare continue handler for not found set more=false;
        open cur2;
    L2: while (true) do 
            fetch cur2 into v_name, v_course_id;
            if more=false then
                leave L2;
            end if;
            if v_name != v_prev_name then
                insert into report (name) values(v_name);
                set v_prev_name = v_name;
            end if;
            set v_course_id = replace(v_course_id,'-','_');             
            set @stmt = concat('update report ',
                               'set ', v_course_id, '=''X'' ',
                               'where  name =''', v_name, ''' ' );
            prepare statement from @stmt;
            execute statement;
            deallocate prepare statement;
        end while;
        close cur2;
    end;
end
/
call flatten()
/
select * from report