For example: Name | 1stjan | 2ndjan | 3rdjan | 4thjan jenny | present | absent | present | present clark | absent | present | present | present Original tables - emp_details id | emp_id | Name 1 | 51 | jenny 2 | 52 | clark Original tables - emp_attendance id | emp_id | status | Date 1 | 51 | present | 2017/01/02 2 | 52 | present | 2017/01/02
DELIMITER $$ DROP PROCEDURE IF EXISTS `attendance_reg`.`new2` $$ CREATE PROCEDURE new2 (IN ddl1 varchar(100)) BEGIN SET @sql = NULL; SET @vardomain := CONCAT(ddl1,'%'); select GROUP_CONCAT(DISTINCT CONCAT(' Max(CASE WHEN DATE_FORMAT(b.time_in, ''%D %M'') = ''', DATE_FORMAT(time_in, '%D %M'), ''' THEN b.status END) AS ''', DATE_FORMAT(time_in, '%D %M'), '''' ) ORDER BY time_in) INTO @sql from emp_attendance where time_in like @vardomain; SET @sql = CONCAT('SELECT b.emp_id ,a.emp_name ,', @sql, ' from emp_details a join emp_attendance b on b.emp_id=a.emp_id and b.time_in like ',@vardomain,' Group By a.emp_name order by a.emp_dept,a.emp_name'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END $$ DELIMITER ;
SET SESSION GROUP_CONCAT_max_len = 1000000;
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)