This answer isn't dynamic, but shows you how to achieve the goal in pure TSQL, making it dynamic should be straightforward.
Essentially, you just need to add another column to spread the data out so that the MAX(fieldname) isn't the max for the event. I've chosen to use a ROW_NUMBER partitioned by the EventID so that each entry for each event is also ...
Unfortunately, MySQL doesn't have an UNPIVOT operator which would be very handy in this case - you'll just have to roll up your sleeves and do it manually I'm afraid. See the fiddle here - I've only done 3 CFWs - you can just copy and paste for the remainder.
CREATE TABLE example
opp_id INTEGER NOT NULL PRIMARY KEY,
region VARCHAR (10) NOT NULL,
You're only getting one row because there is no other column/value to have PIVOT produce different groups.
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS Dummy
MAX(Name) FOR Occupation IN (Doctor, ...
What you are missing is a row that can be grouped by. Row_NUMBER is a good solution
CREATE TABLE OCCUPATIONS
(Name varchar(255), Occupation varchar(255))
INSERT INTO OCCUPATIONS
WITH Sales_CTE ([50001 Sales], [50002 Sales], [50001 margin],[50002 margin],[50001 profit],[50002 profit],[50001 cost],[50002 cost])
select * from
select rtrim(cast(csstor as char))+' Sales' as s
,rtrim(cast(csstor as char))+' margin' as m
,rtrim(cast(csstor as char))+' profit' as p