How to rename columns names in dynamically pivot table from other table?
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