write a MYSQL query to optimize the following query?

Question

write a MYSQL query to optimize the following query ? the emp_attendance table look like empid,name,month,01,02,03,......31. and its values are 121,robin,jan-2023,P,P,SL,LOP,......upto 31 days. i want result like emp_name,emp_id,P_days,SL_days,LOP_days. like robin,121,(01,02,03),(04),(05,06). here i want which are the dates found the SL,LOP,EL ?

write a aq uery on the above question ? i written the query but it is not upto the mark . so please optimize following the query. here i am taken only 4 days. i have to write upto 31 days so in that case it is not fisible solution.

SELECT
    `empid`,
    `01`,
    `02`,
    `03`,
    `04`,
    date,
    CONCAT_WS(', ',
        CASE WHEN `01` IN ('?P+?LOP') THEN '01' ELSE NULL END,
        CASE WHEN `02` IN ('?P+?LOP') THEN '02' ELSE NULL END,
        CASE WHEN `03` IN ('?P+?LOP') THEN '03' ELSE NULL END,
        CASE WHEN `04` IN ('?P+?LOP') THEN '04' ELSE NULL END
    ) AS '?P+?LOP ',
    CONCAT_WS(', ',
        CASE WHEN `01`IN ('SL') THEN '01' ELSE NULL END,
        CASE WHEN `02` IN ('SL') THEN '02' ELSE NULL END,
        CASE WHEN `03`IN ('SL') THEN '03' ELSE NULL END,
        CASE WHEN `04` IN ('SL') THEN '04' ELSE NULL END
    ) AS 'SL',
    CONCAT_WS(', ',
        CASE WHEN `01`IN ('?LOP+?P') THEN '01' ELSE NULL END,
        CASE WHEN `02` IN ('?LOP+?P') THEN '02' ELSE NULL END,
        CASE WHEN `03`IN ('?LOP+?P') THEN '03' ELSE NULL END,
        CASE WHEN `04` IN ('?LOP+?P') THEN '04' ELSE NULL END
    ) AS '?LOP+?P',
     CONCAT_WS(', ',
        CASE WHEN `01`IN ('?SL+?P') THEN '01' ELSE NULL END,
        CASE WHEN `02` IN ('?SL+?P') THEN '02' ELSE NULL END,
        CASE WHEN `03`IN ('?SL+?P') THEN '03' ELSE NULL END,
        CASE WHEN `04` IN ('?SL+?P') THEN '04' ELSE NULL END
    ) AS '?SL+?P'
FROM
    acc_testing.attendance
GROUP BY
    `id`;

score 2 · Answer 1 · answered May 19 '23 at 13:12

Unpivot values, then use conditional group_concat():

select empid, name, month, 
       group_concat(case when val = 'P'   then col end) P_days,
       group_concat(case when val = 'SL'  then col end) SL_days, 
       group_concat(case when val = 'LOP' then col end) LOP_days 
from emp_attendance
cross join lateral (
  select `01`,  1 union all 
  select `02`,  2 union all 
  select `03`,  3 union all
  select `04`,  4
) as upv(val, col)
group by empid, name, month

dbfiddle demo

write a MYSQL query to optimize the following query?

1 Answers1