/******************************************************************************
  Flatten out any 2 column table 
  First column  is used as the key 
  Second column is used to flatten out the values
  (there are 2 examples below) 
******************************************************************************/
drop procedure flatten_any
/
create procedure flatten_any(p_source_table varchar(50))
begin
    --------------------------------------------------------------------------
    -- Step1: Create a report table with dynamic number of columns
    --        Take the incoming source table, and make a copy of it
    --        For every unique value of the second column
    --        Create a string = 'value_of_column  varchar(25),'
    --        Create the report table
    --------------------------------------------------------------------------
    begin                               
        declare more    boolean default true;           
        declare v_name  varchar(100);
        declare v_value varchar(25);
        declare cur1 cursor for
            select * from source_table;
        declare continue handler for not found set more=false;
        set @stmt = concat('create temporary table source_table as ',
                           'select * from ', p_source_table);
        prepare statement from @stmt;
        execute statement;
        deallocate prepare statement;
        set @cols = '';
        open cur1;
    L1: while (true) do 
            fetch cur1 into v_name, v_value;
            if more=false then
                leave L1;
            end if;
            set v_value = replace(v_value,' ','_');     --space not allowed      
            set v_value = replace(v_value,'-','_');     --dash not allowed
            if locate(v_value, @cols) = 0 then
                set @cols = concat(@cols,v_value,' varchar(25), ');
            end if;
        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 value (col2) 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_value     varchar(25);
        declare cur2 cursor for
            select * from source_table
            order by 1,2; 
        declare continue handler for not found set more=false;
        open cur2;
    L2: while (true) do 
            fetch cur2 into v_name, v_value;
            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_value = replace(v_value,' ','_');   
            set v_value = replace(v_value,'-','_');            
            set @stmt = concat('update report ',
                               'set ', v_value, '=''X'' ',
                               'where  name =''', v_name, ''' ' );
            prepare statement from @stmt;
            execute statement;
            deallocate prepare statement;
        end while;
        close cur2;
    end;
end
/
drop table test_table;
/
--create table test_table as
--select concat(fname,' ',lname), course_id
--  from student join class on ssn=stu_ssn;
/  
create table test_table as
select concat(s.fname,' ',s.lname), concat(i.fname,' ',i.lname)
  from class join student s    on s.ssn=stu_ssn
             join instructor i on i.ssn=inst_ssn;
/  
call flatten_any('test_table')
/
select * from report