title: ambiguous error in transpose row to column in mysql
body: <p>tbl_attendance</p>
  
  <pre><code>CREATE TABLE `tbl_instructor_info` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `InstructorID` varchar(7) NOT NULL,
    `Name` varchar(50) NOT NULL,
    `Mobile` varchar(11) NOT NULL,
    `Email` varchar(50) NOT NULL,
    `Trade` int(1) NOT NULL,
    `TSP` int(1) NOT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE KEY `InstructorID` (`InstructorID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>tbl_assigned</p>
  
  <pre><code>CREATE TABLE `tbl_assigned` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Round` int(2) DEFAULT NULL,
    `TraineeID` varchar(7) DEFAULT NULL,
    `Name` varchar(25) DEFAULT NULL,
    `Mobile` int(10) DEFAULT NULL,
    `BatchID` varchar(35) DEFAULT NULL,
    `Remarks` varchar(8) DEFAULT NULL,
    `District` varchar(15) DEFAULT NULL,
    `Comments` varchar(21) DEFAULT NULL,
    `Level` varchar(2) DEFAULT NULL,
    `Trade` int(1) DEFAULT NULL,
    `Status` int(1) DEFAULT NULL,
    `Photo` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`ID`),
    KEY `TraineeID` (`TraineeID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=356 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>tbl_instructor_info</p>
  
  <pre><code>CREATE TABLE `tbl_attendance` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `TraineeID` varchar(7) NOT NULL,
    `attnDate` date NOT NULL,
    `inTime` time NOT NULL,
    `outTime` time NOT NULL,
    `Status` varchar(10) NOT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=316 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>Getting ambiguous error in <code>TraineeID</code>. I used <code>varchar</code> type data for <code>TraineeID</code>. Where clause also generating error. pleas help me to get this result.  </p>
  
  <pre><code>SET @sql = NULL;
  SELECT
      GROUP_CONCAT(DISTINCT
      CONCAT('MAX(IF(`TraineeID`= ', `TraineeID`, ',"P","A")) AS ID',`TraineeID`)
              ) INTO @sql
      FROM tbl_attendance t1 JOIN tbl_assigned t2 
      ON t1.TraineeID=t2.TraineeID
      WHERE t2.BatchID='ID-Electronics/FMMTTC-01M/R7/01';  
      SET @sql = CONCAT('SELECT DATE_FORMAT(attnDate,"%d-%m-%Y")AS Date, ', @sql, ' 
      FROM tbl_attendance
      WHERE attnDate BETWEEN ',2015-12-01,' and ',2015-12-02,'
      GROUP BY attnDate');
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  </code></pre>
  
  <p>some of <code>TraineeID</code>s in <code>tbl_attendance</code> which are not in <code>tbl_assigned</code> table. Actually <code>TraineeID</code>s in <code>tbl_attendance</code> would match with two data table. one is <code>tbl_assigned</code> table and other one is <code>tbl_Instructor_Info</code>. Should I use like below:</p>
  
  <pre><code>SET @sql = NULL;
  SELECT
      GROUP_CONCAT(DISTINCT
      CONCAT('MAX(IF(`t1.TraineeID`= ', `t1.TraineeID` or `t3.InstructorID`, ',"P","A")) AS ID',`t1.TraineeID`)
              ) INTO @sql
      FROM tbl_attendance t1 
      JOIN tbl_assigned t2 ON t1.TraineeID=t2.TraineeID
      Join tbl_instructor_info t3 ON t1.TraineeID=t3.InstructorID
      WHERE t2.BatchID='ID-Electronics/FMMTTC-01M/R7/01';  
      SET @sql = CONCAT('SELECT DATE_FORMAT(attnDate,"%d-%m-%Y")AS Date, ', @sql, ' 
      FROM tbl_attendance
      WHERE attnDate BETWEEN ',2015-12-01,' and ',2015-12-02,'
      GROUP BY attnDate');
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  </code></pre>
  
  <p>here is the dump without any criteria;
  query:</p>
  
  <pre><code>SET @sql = NULL;
  SELECT
      GROUP_CONCAT(DISTINCT
      CONCAT('MAX(IF(`TraineeID`= ', `TraineeID`, ',"P","A")) AS ID',`TraineeID`)
              ) INTO @sql
      FROM tbl_attendance;  
  
      SET @sql = CONCAT('SELECT DATE_FORMAT(attnDate,"%d-%m-%Y")AS Date, ', @sql, ' 
      FROM tbl_attendance
      WHERE attnDate BETWEEN ',2015-12-01,' and ',2015-12-02,'
      GROUP BY attnDate');
  
  select @sql as aboutToUse;
  
  -- PREPARE stmt FROM @sql;
  -- EXECUTE stmt;
  -- DEALLOCATE PREPARE stmt;
  </code></pre>
  
  <p><strong>dump data:</strong>
      SELECT DATE_FORMAT(attnDate,"%d-%m-%Y")AS Date, MAX(IF(<code>TraineeID</code>= 22779,"P","A")) AS ID22779,MAX(IF(<code>TraineeID</code>= 22780,"P","A")) AS ID22780,MAX(IF(<code>TraineeID</code>= 24321,"P","A")) AS ID24321,MAX(IF(<code>TraineeID</code>= 24870,"P","A")) AS ID24870,MAX(IF(<code>TraineeID</code>= 24871,"P","A")) AS ID24871,MAX(IF(<code>TraineeID</code>= 25099,"P","A")) AS ID25099,MAX(IF(<code>TraineeID</code>= 25199,"P","A")) AS ID25199,MAX(IF(<code>TraineeID</code>= 25304,"P","A")) AS ID25304,MAX(IF(<code>TraineeID</code>= 25308,"P","A")) AS ID25308,MAX(IF(<code>TraineeID</code>= 25515,"P","A")) AS ID25515,MAX(IF(<code>TraineeID</code>= 25549,"P","A")) AS ID25549,MAX(IF(<code>TraineeID</code>= 25552,"P","A")) AS ID25552,MAX(IF(<code>TraineeID</code>= 25560,"P","A")) AS ID25560,MAX(IF(<code>TraineeID</code>= 25571,"P","A")) AS ID25571,MAX(IF(<code>TraineeID</code>= 25592,"P","A")) AS ID25592,MAX(IF(<code>TraineeID</code>= 25594,"P","A")) AS ID25594,MAX(IF(<code>TraineeID</code>= 25597,"P","A")) AS ID25597,MAX(IF(<code>TraineeID</code>= 25600,"P","A")) AS ID25600,MAX(IF(<code>TraineeID</code>= 25710,"P","A")) AS ID25710,MAX(IF(<code>TraineeID</code>= 26105,"P","A")) AS ID26105,MAX(IF(<code>TraineeID</code>= 26145,"P","A")) AS ID26145,MAX(IF(<code>TraineeID</code>= 26147,"P","A")) A 
          FROM tbl_attendance
          WHERE attnDate BETWEEN 2002 and 2001
          GROUP BY attnDate</p>