How to rename columns names in dynamically pivot table from other table? I have two tables pointscategory and points:

    CREATE TABLE  pointcategory (
     categoryID int(20) NOT NULL AUTO_INCREMENT,
     categoryname varchar(50) NOT NULL,
      PRIMARY KEY (categoryID)
    )
    CREATE TABLE points (
    pointID int(11) NOT NULL  AUTO_INCREMENT ,
    value int(50) NOT NULL,
    categoryID int(10) NOT NULL,
    studentId int(10) NOT NULL,
    PRIMARY KEY (pointID),
    FOREIGN  KEY (categoryID) REFERENCES pointcategory (categoryID)    )


I have created pivot table from points table by this procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pivot`()
    SQL SECURITY INVOKER
BEGIN
   SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when categoryID = ''',
      categoryID,
      ''' then value end) AS `',
      categoryID, '`'
    )
  ) INTO @sql
FROM  points;
SET @sql = CONCAT('SELECT date(date), ', @sql, ' 
                  FROM points
                  GROUP BY date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END


The output is:

Now I want to rename the columns names (categoryId) in the above table by categoryname from pointscategory table.


  from date,1..6 to date,categoryname1 ... etc
You should replace CategoryID by CategoryName as column alias:

select group_concat(
           distinct 
               concat('count(case when categoryid = ''',
                      p.categoryid,
                      ''' then value end) AS ',
                      pt.categoryname
                     )
                   ) as cases
from   points p
join   pointcategory pt
on     pt.categoryid = p.categoryid;




count(case when categoryid = '1' then value end) AS cat1,
count(case when categoryid = '2' then value end) AS cat2,
count(case when categoryid = '3' then value end) AS cat3


db&lt;>fiddle here