MySql pivot for unknown number of column headers

I have a MySql query which I would like to pivot dynamically, with Company names as column headers and the company financial fields as row headers.

Currently I have this:

SELECT Company, TotalRevenue, Overhead, TotalJobCosts, GrossProfit 
FROM comp_financials

which gives me this:

Company	TotalRevenue	Overhead	TotalJobCosts	GrossProfit
Chicago’s Best Construction	2098001	363750	1424420	673581
Jones Construction	4509458	1067008	2876568	1632890

and I wish to get something like this:

	Chicago’s Best Construction	Jones Construction
TotalRevenue	2098001	4509458
Overhead	363750	1067008
TotalJobCosts	1424420	2876568
GrossProfit	673581	1632890

with an unknown number of companies and their values (but generally less than 20).

I’ve gotten this far to display the company names as column headers dynamically, but can’t get how to display the row values (row headers are not necessary).

SET @@group_concat_max_len = 32000;
SET @sql = NULL;
SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN Company = ''',
               Company,
               ''' THEN Company ELSE NULL END) AS ',
               CONCAT('`', Company, '`')
               )) INTO @sql
FROM comp_financials;

SET @sql = CONCAT('SELECT ', @sql, ' 
                   FROM comp_financials');

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

Answer

First thing to do is turning columns into rows. Method is simple; just need to use UNION ALL. Something like this:

SELECT 1 as r,company, "TotalRevenue" val, TotalRevenue totals FROM comp_financials UNION ALL
SELECT 2, company, "Overhead", Overhead FROM comp_financials UNION ALL
SELECT 3, company, "TotalJobCosts", TotalJobCosts FROM comp_financials UNION ALL
SELECT 4, company, "GrossProfit", GrossProfit FROM comp_financials

I’ve added numbering (.. as r) for later use in ORDER BY. val values corresponds to the column name that I’m retrieving data from. This query will return data like the following:

r	company	val	totals
1	Jones Construction	TotalRevenue	4509458
1	Chicago’s Best Construction	TotalRevenue	2098001
2	Jones Construction	Overhead	1067008
2	Chicago’s Best Construction	Overhead	363750
3	Jones Construction	TotalJobCosts	2876568
3	Chicago’s Best Construction	TotalJobCosts	1424420
4	Jones Construction	GrossProfit	1632890
4	Chicago’s Best Construction	GrossProfit	673581

The query above I make as a derived query for the original query you were doing. So the end result looks like this:

SELECT r,val,
       MAX(CASE WHEN company="Jones construction" THEN totals END) AS "Jones construction",
       MAX(CASE WHEN company="Chicago's Best Construction" THEN totals END) AS "Chicago's Best Construction"
FROM
(SELECT 1 as r,company, "TotalRevenue" val, TotalRevenue totals FROM comp_financials UNION ALL
SELECT 2, company, "Overhead", Overhead FROM comp_financials UNION ALL
SELECT 3, company, "TotalJobCosts", TotalJobCosts FROM comp_financials UNION ALL
SELECT 4, company, "GrossProfit", GrossProfit FROM comp_financials) B
GROUP BY r,val
ORDER BY r ASC

Additionally, I’ve grouped by r and val then order it by r. This query returns data like the following:

r	val	Jones construction	Chicago’s Best Construction
1	TotalRevenue	4509458	2098001
2	Overhead	1067008	363750
3	TotalJobCosts	2876568	1424420
4	GrossProfit	1632890	673581

Adding this to your prepared statement:

SET @@group_concat_max_len = 32000;
SET @sql = NULL;
SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN Company = "',
               Company,
               '" THEN totals ELSE NULL END) AS ',
               CONCAT('"', Company, '"')
               )) INTO @sql
FROM comp_financials;

SET @sql = CONCAT('SELECT r,val, ', @sql, '
   FROM
(SELECT 1 as r,company, "TotalRevenue" val, TotalRevenue totals FROM comp_financials UNION ALL
SELECT 2, company, "Overhead", Overhead FROM comp_financials UNION ALL
SELECT 3, company, "TotalJobCosts", TotalJobCosts FROM comp_financials UNION ALL
SELECT 4, company, "GrossProfit", GrossProfit FROM comp_financials) B
GROUP BY r,val
ORDER BY r ASC');
select @sql;

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

Here’s a demo fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=678de8991e21ccd90d2195d3d5eca0a7

P/S: Next time, please don’t post image for data tables or anything (codes) that we could copy and paste. Fortunately this only have a couple of data rows so it’s easier to re-create but not many are willing to help if they see photos instead.