MySQL CrossTab results contain non-distinct columns

Question

Two part question:

In producing a cross tab results in MySql, the Distinct call doesn't seem to be working... or I'm missing something else? I'm getting the same ClassName in several columns. i.e. "MDC (intro)".
: Existing example in SQLFiddle produces results (although not correct), however when moved to hosted MySql it fails with error: #1064 - 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 'el FROM EnrollmentsTblx GROUP BY AutoNum' at line 1

SQL:

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


SET @sql = CONCAT('SELECT AutoNum, UserName, ', @sql, ' 
              FROM EnrollmentsTbl 
              GROUP BY AutoNum, UserName');

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

Schems:

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

Fiddle Screen Shot:

I have this working in an Access DB as SQL:

TRANSFORM DateCompleted
SELECT UserName
FROM EnrollmentsTbl
GROUP BY UserName
ORDER BY UserName
PIVOT ClassName

The desired results look something like this:

UserName    MDC (Intro)     HIPAA (Employee)    Firehouse (Incident)
--------    -----------     ----------------    --------------------
John        6-27-2004       10-4-2006           8-6-2005
Bob         6-27-2004                           
Robert      6-27-2004                           8-6-2005
Debbie      4-19-2003
Jeff                                            11-25-2006
Tom                         7-13-2004
Janet                       11-25-2006

Drew · Answer 1 · 2016-07-02T20:53:15.670

Your string has an escaped \ before every single quote.

To prove that, I dumped it to a text editor (MySQL Workbench) where it was obvious after creating your table. I did a ctrl-H for Find and Replace, (\' to a '), and was able to execute your prepared stmt and get the resultset.

This is a common problem when moving data between systems (or shells, or various programming languages). I also noticed a few \n at the end of data.

Also, DISTINCT works across all columns (not each column individually).

So distinct on a two column output would result in the following easily:

class    student
-------  ----------
ABC      Drew
ABC      Barlay

look into the mysql replace() function, or:

SELECT 
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
      ' GROUP_CONCAT((CASE ClassName when \'',  
      ClassName,  
      '\' then \'',DateCompleted,'\' else NULL END)) AS `',
      ClassName, '`'
    )
  ) INTO @sql
FROM EnrollmentsTbl;
SET @sql = CONCAT("SELECT AutoNum, UserName, ", @sql, " FROM EnrollmentsTbl GROUP BY AutoNum, UserName");

select @sql;

renders:

SELECT AutoNum, UserName,  GROUP_CONCAT((CASE ClassName when 'MDC (Intro)' then '2004-06-27 00:00:00' else NULL END)) AS `MDC (Intro)`, GROUP_CONCAT((CASE ClassName when 'HIPAA (Employee)' then '2010-08-04 06:11:10' else NULL END)) AS `HIPAA (Employee)`, GROUP_CONCAT((CASE ClassName when 'MDC (Intro)' then '2003-04-19 14:53:55' else NULL END)) AS `MDC (Intro)`, GROUP_CONCAT((CASE ClassName when 'MDC (Intro)' then '2003-03-29 14:26:23' else NULL END)) AS `MDC (Intro)`, GROUP_CONCAT((CASE ClassName when 'Firehouse (Incident)' then '2004-07-13 00:00:00' else NULL END)) AS `Firehouse (Incident)`, GROUP_CONCAT((CASE ClassName when 'MDC (On-Line)' then '2016-06-30 14:02:05' else NULL END)) AS `MDC (On-Line)` FROM EnrollmentsTbl GROUP BY AutoNum, UserName
-- Drew used above

Worked like a charm for the escaped sequence side of things.

For a little insight as to why double quotes were used on the concat(), well, using single quotes caused the prev value of @sql to have its single quotes escaped with a \ thus probably causing a 1064 error on your system. So you might have been homefree if it weren't for the 2nd part of putting the string together.

Always pause and look at strings between steps to see what state things are in, and what is un-doing a prior step.

Now on to your DISTINCT data problem (that is for a new question on the stack, not here)

look in the Fiddle ScreenShot at the @sql line, its not there? - that being said, do you know why the DISTINCT is not working? — BarclayVision, Jul 02 '16 at 19:21
If you need to discuss this further or I am being dense, you can find me in the [Campaigns](http://chat.stackoverflow.com/rooms/95290/campaigns) chat room. — Drew, Jul 02 '16 at 19:48
I did get it to run under MySQLWorkbench however it produced the same duplicate columns. — BarclayVision, Jul 02 '16 at 21:34
There is nothing not distinct about that data as far as mysql is concerned. — Drew, Jul 02 '16 at 21:39
Maybe I'm using the wrong terminology - I have an access DB with crosstab query - or pivot and it produces a single column for each distinct CalssName. — BarclayVision, Jul 02 '16 at 22:49
Your supplied data (the 8 rows) do not support what you are suggesting. Perhaps what you mean is some other data that you have. — Drew, Jul 03 '16 at 00:52

score 0 · Answer 2 · edited May 23 '17 at 11:51

This seem to be the closest I have come to the desired results, only issue is it returns a count and not the completed date:

SELECT  `UserName`,
COUNT(
    CASE 
        WHEN `ClassName`='MDC (Intro)' 
        THEN `DateCompleted` 
        ELSE NULL 
    END
) AS 'MDC',
COUNT(
    CASE 
        WHEN `ClassName`='HIPAA(Employee)' 
        THEN `DateCompleted` 
        ELSE NULL 
    END
) AS 'HIPAA',
COUNT(
    CASE 
        WHEN `ClassName`='Firehouse (Incident)' 
        THEN `DateCompleted`
        ELSE NULL 
    END
) AS 'Firehouse'
FROM    EnrollmentsTbl
GROUP BY `UserName`;

SQLFiddle Example

example from: MySQL pivot table

score 0 · Accepted Answer · answered Jul 03 '16 at 01:44

Not as dynamic as I wanted, but formatted as desired.

Resolved:

SELECT  `UserName`,
    MAX(IF(`ClassName`='MDC (Intro)', `DateCompleted`, NULL)) AS 'MDC', 
    MAX(IF(`ClassName`='HIPAA (Employee)', `DateCompleted`, NULL)) AS 'HIPAA',
    MAX(IF(`ClassName`='MDC (On-Line)', `DateCompleted`, NULL)) AS 'MDC-OL',
    MAX(IF(`ClassName`='Firehouse (Incident)', `DateCompleted`, NULL)) AS 'Fierhouse'
FROM    EnrollmentsTbl
GROUP BY `UserName`;

working example: SQLFiddle

MySQL CrossTab results contain non-distinct columns

3 Answers3