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