Convert MySQL to Laravel query. (Flatten rows to column concept)

I need to convert this MySQL query to Laravel query. Using Flatten row to column concepts. enter image description here Here, 3 tables are their bonus, employee, bonus_map as input and Output table is to get from the input.

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN bgm.bonusId =',bonusId,' THEN bgm.bonusAmt ELSE 0 END) AS `', bonusName,'`')) INTO @sql FROM (SELECT b.id AS bonusId,b.bonusScheme as bonusName from bonus b inner join bonus_group_mapping bgm on b.id = bgm.bonusId ) bonus;  SET @sql = CONCAT('SELECT e.employeeId, e.firstname, ', @sql, ' FROM employee_details e LEFT JOIN bonus_group_mapping bgm ON e.id = bgm.employeeId LEFT JOIN increment_group_mapping igm ON e.id = igm.employeeId WHERE e.planId = 10 GROUP BY e.id, e.firstname;');  PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.