1

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');
0
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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.