Mysql Query using Pivot table

Question

i have the ff. data..

    empID    Login Date    TimeIN    TimeOut    
    1001     01/01/2017    08:00     17:00  
    1001     01/02/2017    07:59     17:02  
    1001     01/03/2017    07:54     17:05  
    1001     01/04/2017    08:00     17:04  
    1001     01/05/2017    07:56     17:03  
    1001     01/06/2017    07:52     17:01    
    1001     01/07/2017    07:53     17:02

i want the output to be something like this..

    EmpID   mon           tue          wed           thu            fri   
    1001    08:00-17:00   7:59-17:02   07:54-17:05   08:00-17:04    07:56-17:03

i have try using this Query..

    select cEmpID,dDate,   
    (case DAYOFWEEK(dDate) WHEN 1 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Mon',
    (case DAYOFWEEK(dDate) WHEN 2 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Tue',
    (case DAYOFWEEK(dDate) WHEN 3 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Wed',
    (case DAYOFWEEK(dDate) WHEN 4 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Thu',
    (case DAYOFWEEK(dDate) WHEN 5 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Fri',
    (case DAYOFWEEK(dDate) WHEN 6 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Sat',
    (case DAYOFWEEK(dDate) WHEN 7 then CONCAT(cIn1,' - ',cOut2) else '' End) as 'Sun'  
    from tblattenddetail  
    Where cPeriodID='201702'
    GROUP BY cEmpID

But it does not Work..

Why are you pulling dDate in select list when you don't want it into the result? — Ankit Bajpai, Jul 14 '17 at 07:42
check this answer , `https://stackoverflow.com/questions/42408465/need-to-convert-columns-into-rows-in-mysql/42409047#42409047` — chirag satapara, Jul 14 '17 at 08:00
@chiragsatapara, This query can be solved by using case only. No need to use pivot here. — Ankit Bajpai, Jul 14 '17 at 08:03

score 0 · Answer 1 · answered Jul 14 '17 at 08:19

Please try below query -

SELECT empID,  
       MAX(case DAYOFWEEK(`Login Date`) WHEN 1 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Mon',
       MAX(case DAYOFWEEK(`Login Date`) WHEN 2 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Tue',
       MAX(case DAYOFWEEK(`Login Date`) WHEN 3 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Wed',
       MAX(case DAYOFWEEK(`Login Date`) WHEN 4 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Thu',
       MAX(case DAYOFWEEK(`Login Date`) WHEN 5 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Fri',
       MAX(case DAYOFWEEK(`Login Date`) WHEN 6 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Sat',
       MAX(case DAYOFWEEK(`Login Date`) WHEN 7 then CONCAT(TimeIN,' - ',TimeOut) else '' End) as 'Sun'  
FROM login
GROUP BY empID

Here is the fiddle for your reference - http://www.sqlfiddle.com/#!9/437e5/5

Mysql Query using Pivot table

1 Answers1