drop procedure if exists pivot_data;
delimiter $$
create procedure pivot_data
( IN pivot_source varchar(1024),
  IN pivot_basefields varchar(81))
begin
set @pivot_sql := 
'select #pivot_basefields#,
#pivot_aliases#
from
(
select #first_user_dets#,
#pivot_fields#
from 
( #pivot_source# ) #first_alias#
inner join
#all_joins#
) q
order by #pivot_basefields#
;';

set @pivot_sql := replace(@pivot_sql, '#pivot_source#', pivot_source);
set @pivot_sql := replace(@pivot_sql, '#pivot_basefields#', pivot_basefields);

select group_concat('#last_alias#.', fieldname, ' = #alias#.', fieldname SEPARATOR ' and ') into @base_join_condition
from
(
select seq.n, trim(substring_index(substring_index(t.pivot_basefields, ',', seq.n), ',', -1)) as fieldname
from ( select pivot_basefields ) t 
cross join
(
  select a1.n * 10 + a0.n + 1 n
  from digits_v a1
  cross join digits_v a0
  order by n
) seq
where seq.n <= 1 + (length(t.pivot_basefields) - length(replace(t.pivot_basefields, ',', '')))
) split
;
                          
set @pivot_block := 
'( #pivot_source# ) #alias# 
on  1 = 1
and #base_join_condition#
and #last_alias#.pivot_fid = #alias#.pivot_fid - 1 
inner join #all_joins#';

set @pivot_block := replace(@pivot_block, '#pivot_source#', pivot_source);
set @pivot_block := replace(@pivot_block, '#base_join_condition#', @base_join_condition);

set @qry_write_joins := replace('select count(*) into @ignore
from
(
select 
@pivot_sql := replace(@pivot_sql, ''#all_joins#'', replace(replace(@pivot_block, ''#alias#'', concat(''pivot'', right_id)), ''#last_alias#'', concat(''pivot'', left_id)))
from
(
select `left`.pivot_fid as left_id, min(`right`.pivot_fid) as right_id
from ( #pivot_source# ) `left`
inner join ( #pivot_source# ) `right`
on `left`.pivot_fid = `right`.pivot_fid - 1
group by 1
) t
order by left_id
) `ignore`;', '#pivot_source#', pivot_source);
prepare write_joins from @qry_write_joins;
EXECUTE write_joins;
deallocate prepare write_joins;

set @qry_get_first_alias := replace('select concat(''pivot'', pivot_fid) into @first_alias
from ( #pivot_source# ) p
order by pivot_fid
limit 1;', '#pivot_source#', pivot_source);
prepare get_first_alias from @qry_get_first_alias;
EXECUTE get_first_alias;
deallocate prepare get_first_alias;
                          
select group_concat(@first_alias, '.', fieldname SEPARATOR ',') into @first_user_dets
from
(
select seq.n, trim(substring_index(substring_index(t.pivot_basefields, ',', seq.n), ',', -1)) as fieldname
from ( select pivot_basefields ) t 
cross join
(
  select a1.n * 10 + a0.n + 1 n
  from digits_v a1
  cross join digits_v a0
  order by n
) seq
where seq.n <= 1 + (length(t.pivot_basefields) - length(replace(t.pivot_basefields, ',', '')))
) split
;
                    
-- select concat(@first_alias, '.user_id,',@first_alias,'.username') into @first_user_dets;

set @qry_get_pivot_fields := replace('select group_concat(''pivot'', pivot_fid, ''.pivot_value `'', pivot_fname, ''`'' SEPARATOR '','') into @pivot_fields
from (
select distinct pivot_fid, pivot_fname
from ( #pivot_source# ) q ) p;', '#pivot_source#', pivot_source);
prepare get_pivot_fields from @qry_get_pivot_fields;
EXECUTE get_pivot_fields;
deallocate prepare get_pivot_fields;

set @qry_get_pivot_aliases = replace('select group_concat(''`'', pivot_fname, ''`'' order by pivot_fid SEPARATOR '','') into @pivot_aliases
from (
select distinct pivot_fid, pivot_fname
from ( #pivot_source# ) q ) p;', '#pivot_source#', pivot_source);
prepare get_pivot_aliases from @qry_get_pivot_aliases;
EXECUTE get_pivot_aliases;
deallocate prepare get_pivot_aliases;

select count(*) into @ignore
from
(
select
@pivot_sql := replace(@pivot_sql, '#first_user_dets#', @first_user_dets),
@pivot_sql := replace(@pivot_sql, '#pivot_fields#', @pivot_fields),
@pivot_sql := replace(@pivot_sql, '#pivot_aliases#', @pivot_aliases),
@pivot_sql := replace(@pivot_sql, '#first_alias#', @first_alias),
@pivot_sql := replace(@pivot_sql, 'inner join #all_joins#', '')
) `ignore`
;

-- select @pivot_sql;
  
prepare pivot_sql from @pivot_sql;
EXECUTE pivot_sql;
deallocate prepare pivot_sql;

end $$
delimiter ;

pivot data. input source query in the form :

    +--------+-----------+-------------+-------------+
    | name   | pivot_fid | pivot_fname | pivot_value |
    +--------+-----------+-------------+-------------+
    | apple  |         1 | is red      |           1 |
    | apple  |         2 | is blue     |           0 |
    | orange |         1 | is red      |           1 |
    | orange |         2 | is blue     |           0 |
    +--------+-----------+-------------+-------------+

and fields to pivot around ( eg. name ), outputs :

    +--------+--------+---------+
    | name   | is red | is blue |
    +--------+--------+---------+
    | apple  |      1 |       0 |
    | orange |      1 |       0 |
    +--------+--------+---------+

mysql pivot data

mysql

SQL