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 ;