Skip to content
Advertisement

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;

Advertisement

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.

5 People found this is helpful
Advertisement