Ok, so I have three tables I'm working on combining to create my pivot table. For simplicity, I'll only reference the fields I'm using for this.
employees->userTblID, firstName, lastName
lms__trainings->trainingID, trainingName
lms__complete->completeID, trainingID, employeeID(userTblID)
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(b.trainingID = ''',F.trainingID, ''', b.completionDate, NULL)) AS ''', F.trainingName,"'")) INTO @sql FROM lms__trainings AS F INNER JOIN lms__complete AS G ON F.trainingID=G.trainingID;
SET @sql = CONCAT('SELECT a.userTblID, a.firstName, a.lastName, ', @sql, ' FROM employees AS a LEFT JOIN lms__complete AS b ON a.userTblID=b.employeeID WHERE a.cloudID=1 GROUP BY a.userTblID ORDER BY a.lastName');
PREPARE stmt FROM @sql;
EXECUTE stmt;
It generates the PIVOT table fine, but it only includes trainingName columns that have an associated record in lms__complete. If I remove the inner join from line 2 or change it to a left join (which in my thought would not limit it to lms__complete entries and give you all the trainingNames in lms__trainings) it gives the good old 1064 error.
I know it's just not sending all trainingIDs through the procedure. I'm thinking my LEFT JOIN on line 3 should be a LEFT JOIN of a UNION between lms__trainings and lms__complete, but I haven't put the correct search parameters in google for the last couple days to find what I need.