Mysql version 8.0.12
sproc code complete without error
CREATE DEFINER=`root`@`%` PROCEDURE `SP`()
BEGIN
DECLARE tablename LONGTEXT;
DECLARE table_name LONGTEXT;
DECLARE tyear INT(4);
SET SESSION group_concat_max_len = 1000000;
SET GLOBAL connect_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;
SET GLOBAL wait_timeout = 28800;
SET tyear = YEAR(CURDATE());
SET @tbl = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H'),'_',tyear);
SET tablename = CONCAT('t_contents_',@tbl,'');
SET table_name = CONCAT('t_contents_q_,@tbl,'');
FLUSH HOSTS;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')
) INTO @sql
FROM t_contents_2021;
FLUSH HOSTS;
SET @sql = CONCAT('SELECT ', @sql,'
FROM
(
SELECT
tcs.rn,
tcs.puni,
tcs.pname,
replace(fnStripTags(tcs.contents)," ","") contents
FROM
`', table_name, '` AS q
INNER JOIN ( SELECT puni, pname, contents, ROW_NUMBER() OVER ( PARTITION BY puni ORDER BY sID ) AS rn FROM `', tablename, '` ) AS tcs ON tcs.puni = q.puni
) AS `', tablename, '`
GROUP BY
rn
ORDER BY
rn;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @sql;
FLUSH HOSTS;
END
sproc code complete with error
CREATE DEFINER=`root`@`%` PROCEDURE `SP`()
BEGIN
DECLARE tablename LONGTEXT;
DECLARE table_name LONGTEXT;
DECLARE tyear INT(4);
SET SESSION group_concat_max_len = 1000000;
SET GLOBAL connect_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;
SET GLOBAL wait_timeout = 28800;
SET tyear = YEAR(CURDATE());
SET @tbl = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H'),'_',tyear);
SET tablename = CONCAT('t_contents_',@tbl,'');
SET table_name = CONCAT('t_contents_q_,@tbl,'');
FLUSH HOSTS;
SET @sql = NULL;
SET @GetName = CONCAT('
SELECT
GROUP_CONCAT(DISTINCT
CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')
) INTO @sql
FROM `', tablename, '`;');
SELECT @GetName;
PREPARE stmt FROM @GetName;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FLUSH HOSTS;
SET @sql = CONCAT('SELECT ', @sql,'
FROM
(
SELECT
tcs.rn,
tcs.puni,
tcs.pname,
replace(fnStripTags(tcs.contents)," ","") contents
FROM
`', table_name, '` AS q
INNER JOIN ( SELECT puni, pname, contents, ROW_NUMBER() OVER ( PARTITION BY puni ORDER BY sID ) AS rn FROM `', tablename, '` ) AS tcs ON tcs.puni = q.puni
) AS `', tablename, '`
GROUP BY
rn
ORDER BY
rn;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @sql;
FLUSH HOSTS;
END
this part edited of sproc code this the problem when I try to using variable name table `', tablename, '` instead of `t_contents_2021`
SET @GetName = CONCAT('
SELECT
GROUP_CONCAT(DISTINCT
CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')
) INTO @sql
FROM `', tablename, '`;');
PREPARE stmt FROM @GetName;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;