BarclayVision BarclayVision - 5 months ago 27
MySQL Question

MySQL CrossTab Results

Looking for a little help on returning cross tab results from MySQL query, I have used MS Access and Pivot tables in the past that work fine. I'm moving over to MySQL and need to obtain the same results. I have found mysql pivot/crosstab query that is exactly what I'm trying to achieve, but seem to get errors in my table. (the links to SQLFiddle in the SO example error out)

SET SESSION group_concat_max_len = 10000;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' GROUP_CONCAT((CASE Class_Name when ', CHAR(39),
ClassName, CHAR(39),
' then ', CHAR(39), DateCompleted, CHAR(39), ' else NULL END)) AS Completed',
ClassName
)
) INTO @sql
FROM EnrollmentsTbl;


Schema:

SET NAMES 'UTF8';


CREATE TABLE `EnrollmentsTbl` (
`AutoNum` INTEGER PRIMARY KEY,
`UserName` VARCHAR(50),
`SubmitTime` DATETIME,
`ClassName` VARCHAR(50),
`ClassDate` DATETIME,
`ClassTime` VARCHAR(50),
`Enrolled` BOOLEAN,
`WaitListed` BOOLEAN,
`Instructor` VARCHAR(50),
`DateCompleted` DATETIME,
`Completed` BOOLEAN,
`EnrollmentsMisc` VARCHAR(50),
`Walkin` BOOLEAN
) CHARACTER SET 'UTF8';

INSERT INTO `EnrollmentsTbl`(`AutoNum`,`UserName`,`SubmitTime`,`ClassName`,`ClassDate`, `ClassTime`,`Enrolled`,`WaitListed`,`Instructor`,`DateCompleted`,`Completed`,`EnrollmentsMisc`,`Walkin`)
VALUES(1,'John',NULL,'MDC (Intro)','2004-06-27 00:00:00',NULL,TRUE,FALSE,'Phil','2004-06-27 00:00:00',TRUE,NULL,FALSE),
(2,'Bob',NULL,'MDC (Intro)','2004-06-27 00:00:00',NULL,TRUE,FALSE,'Phil','2004-06-27 00:00:00',TRUE,NULL,FALSE),
(3,'Robert',NULL,'MDC (Intro)','2004-06-27 00:00:00',NULL,TRUE,FALSE,'Phil','2004-06-27 00:00:00',TRUE,NULL,FALSE),
(4,'John','2010-08-04 06:11:10','HIPAA(Employee)','2010-08-04 00:00:00','6:12 AM',TRUE,FALSE,'On-line','2010-08-04 06:11:10',TRUE,NULL,FALSE),
(5,'Debbie',NULL,'MDC (Intro)','2003-04-19 14:53:55',NULL,TRUE,FALSE,'devore','2003-04-19 14:53:55',TRUE,NULL,FALSE),
(6,'Jeff',NULL,'MDC (Intro)','2003-03-29 14:26:23',NULL,TRUE,FALSE,'','2003-03-29 14:26:23',TRUE,NULL,FALSE),
(7,'Tom',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'Shannon','2004-07-13 00:00:00',TRUE,NULL,FALSE),
(8,'Rhonda',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'arobe','2004-07-13 00:00:00',TRUE,NULL,FALSE),
(9,'Jeff',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'arobe','2004-07-13 00:00:00',TRUE,NULL,FALSE),
(10,'Patrick',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'arobe','2004-07-13 00:00:00',TRUE,NULL,FALSE),
(11,'Donnie',NULL,'Firehouse (Incident)','2004-07-10 00:00:00',NULL,TRUE,FALSE,'feiertag','2004-07-10 00:00:00',TRUE,NULL,FALSE),
(12,'Andy',NULL,'Firehouse (EMS)','2004-07-10 00:00:00',NULL,TRUE,FALSE,'feiertag','2004-07-10 00:00:00',TRUE,NULL,FALSE),
(13,'Brian',NULL,'Firehouse (Incident)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
(14,'Jane',NULL,'Firehouse (EMS)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
(15,'Richard',NULL,'Firehouse (EMS)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
(16,'Dale',NULL,'Firehouse (EMS)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
(17,'Stinky','2016-06-29 17:17:19','FireApp (Assessment Only)','2016-07-18 00:00:00','1830',TRUE,FALSE,NULL,NULL,FALSE,NULL,FALSE),
(18,'Janet','2016-06-30 14:02:05','MDC (On-Line)','2016-06-30 00:00:00','2:02 PM',TRUE,FALSE,'On-line','2016-06-30 14:02:05',TRUE,NULL,FALSE);


here is my SQLFiddle example that fails: http://sqlfiddle.com/#!9/0c4c2/3

UPDATE:
here is the @sql returned and error:
@sql
SELECT AutoNum, UserName, GROUP_CONCAT((CASE Class_Name when 'MDC (Intro)' then '2004-06-27 00:00:00'else NULL END)) AS CompletedMDC (Intro), GROUP_CONCAT((CASE Class_Name when 'HIPAA (Employee)' then '2010-08-04 06:11:10'else NULL END)) AS CompletedHIPAA (Employee), GROUP_CONCAT((CASE Class_Name when 'MDC (Intro)' then '2003-04-19 14:53:55'else NULL END)) AS CompletedMDC (Intro), GROUP_CONCAT((CASE Class_Name when 'MDC (Intro)' then '2003-03-29 14:26:23'else NULL END)) AS CompletedMDC (Intro), GROUP_CONCAT((CASE Class_Name when 'Firehouse (Incident)' then '2004-07-13 00:00:00'else NULL END)) AS CompletedFirehouse (Incident), GROUP_CONCAT((CASE Class_Name when 'MDC (On-Line)' then '2016-06-30 14:02:05'else NULL END)) AS CompletedMDC (On-Line) FROM enrollmentstbl GROUP BY AutoNum, UserName


Record Count: 1; Execution Time: 1ms View Execution Plan link
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Intro), GROUP_CONCAT((CASE Class_Name when 'HIPAA (Employee)' then '2010-08-04 ' at line 1

Answer

The issue is that in the linked topic the field that gets evaluated is numeric (node_id), while in your case it is textual (classname). But in your code you do not enclose the values coming from this field by single or double quotes, therefore MySQL cannot really interpret them, hence the error message.

I added char(39) calls to the sql creating the group_concat() calls. Char(39) is the apostrophe (single quote). Unfortunately I cannot access sqlfiddle to check if it works now. But do a select @sql command before trying to create a prepared statement out of @sql and you can check and test the resulting sql statement yourself.

You may have to enclose the classname the 2nd time by single quotes as well.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE Class_Name when ', CHAR(39), 
      ClassName, CHAR(39),
      ' then DateCompleted else NULL END)) AS Completed',
      ClassName
    )
  ) INTO @sql
FROM EnrollmentsTbl;

UPDATE

The 2nd issue is that your classname field values contain space and other non-conventional characters, such as parentheses and these play havoc with your field name aliases.

For example, you have the following alias in your sql:

...AS CompletedHIPAA (Employee)...

You need to enclose such aliases by the backtick character (`) to work:

...`AS CompletedHIPAA (Employee)`...

Modified sql to include backticks:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE Class_Name when ', CHAR(39), 
      ClassName, CHAR(39),
      ' then DateCompleted else NULL END)) AS `Completed',
      ClassName,'`'
    )
  ) INTO @sql
FROM EnrollmentsTbl; 
Comments