Indexing columns to make a MySQL query faster
Currently your code scans the spooner_pec table 6 times, once each for the year-based columns, and again for the final result.

You can reduce this to just 2 times by caching the list of years in a temp table:

CREATE TEMPORARY TABLE GotYears (year varchar(10));

INSERT INTO GotYears
SELECT DISTINCT year
FROM spooner_pec


Now you have a small table with just a few years in it, instead of 3,000,000 records. You have scanned the table just once to do that too.

Now use that table to build your SQL select clauses....

SELECT GROUP_CONCAT(CONCAT(
     'max(case when year = ', year, ' then experience_rate end) AS `', year, '-Pen`' )
ORDER BY year ) INTO @sql1 FROM GotYears;


Having made that recommendation, though, I also recommend having an index on the year column which will make the scan a whole lot faster too.

The query is still really ugly ™ but that's a problem in your application and data storage.

Additionally, your final query is horrible, horrible, and horrible. A group-by query with many columns, but only grouping by the policy_id is very messy, and is MySQL specific. The values returned in the other columns are unspecified in terms of behaviour. I hope all your values in all the columns are the same or otherwise your output values are unspecified. It is a problem with your database structure too, that the data is not normalized. Why do you record the same values for the same policies in multiple records? Your database may be a data warehouse, but even then it is a bit of a mess.

You should audit your tables, and reconsider the design and normalization of your tables.

Still, if you apply the changes I suggest (add the year index, and have a single temp table for the year values), then I imagine your query will execute in about 5 seconds instead of 30.