Create A Pivot With No SUM() in MySQL

Question

I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL

Create Table `data`
(
   callDate text
  ,callerName text
  ,callLength text
);

Insert Into `data` (`callDate`, `callerName`, `callLength`) Values
('07/01/2020', 'Client A', '0:00:23'),
('07/01/2020', 'Client B', '0:15:23'),
('07/01/2020', 'Client C', '0:7:10'),
('07/02/2020', 'Client A', '0:01:23'),
('07/02/2020', 'Client B', '0:12:23'),
('07/02/2020', 'Client C', '0:3:10'),
('07/03/2020', 'Client A', '0:011:23'),
('07/03/2020', 'Client B', '0:22:23'),
('07/03/2020', 'Client C', '0:23:10');

Answer 1

As i said in the comment simply Replace the SUM with what ever you need in your

Create Table `data`
(
   callDate text
  ,callerName text
  ,callLength text
);

Insert Into `data` (`callDate`, `callerName`, `callLength`) Values
('07/01/2020', 'Client A', '0:00:23'),
('07/01/2020', 'Client B', '0:15:23'),
('07/01/2020', 'Client C', '0:7:10'),
('07/02/2020', 'Client A', '0:01:23'),
('07/02/2020', 'Client B', '0:12:23'),
('07/02/2020', 'Client C', '0:3:10'),
('07/03/2020', 'Client A', '0:011:23'),
('07/03/2020', 'Client B', '0:22:23'),
('07/03/2020', 'Client C', '0:23:10');

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL)) AS "',
          callDate,'"'
        )
  ) INTO @sql
FROM
   `data`;
SET @sql = CONCAT('SELECT callerName,', @sql, ' FROM  `data` GROUP BY callerName');

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

callerName | 07/01/2020 | 07/02/2020 | 07/03/2020
:--------- | :--------- | :--------- | :---------
Client A   | 0:00:23    | 0:01:23    | 0:011:23  
Client B   | 0:15:23    | 0:12:23    | 0:22:23   
Client C   | 0:7:10     | 0:3:10     | 0:23:10   

db<>fiddle here

It is quite ugly and it has the same limitation as the explained here

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL)) AS "',
          callDate,'"'
        )
  ) INTO @sql
  FROM
   `data`;
  SET @sql2 = NULL;
  SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL), "%H:%i:%s")))) AS "',
          callDate,'"'
        )
  ) INTO @sql2
FROM
   `data`;
SELECT @sql2;
SET @sql = CONCAT('SELECT callerName,', @sql, ' FROM  `data` GROUP BY callerName UNION SELECT "total",',@sql2,' FROM  `data`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

| @sql2                                                                                                                                                                                                                                                                                                                                                                         |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/01/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/01/2020",SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/02/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/02/2020",SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/03/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/03/2020" |

callerName | 07/01/2020 | 07/02/2020 | 07/03/2020
:--------- | :--------- | :--------- | :---------
Client A   | 0:00:23    | 0:01:23    | 0:011:23  
Client B   | 0:15:23    | 0:12:23    | 0:22:23   
Client C   | 0:7:10     | 0:3:10     | 0:23:10   
total      | 00:22:56   | 00:16:56   | 00:56:56  

db<>fiddle here