-2

I am working on Classes management project. Everything is done successfully but only stuck with attendance report work.

i want result like this

enter image description here

My table structure:

enter image description here

Rahul Pansare
  • 1
  • 2
  • You are looking for a pivot query, possibly a dynamic one, if the number of months needs to be flexible based on the data set. Are the number of months fixed in your problem? – Tim Biegeleisen Mar 03 '17 at 09:34
  • i have tried lot of things but not getting desired result. i am working on this since last 7 days. finally posted a question here. – Rahul Pansare Mar 03 '17 at 09:35
  • months will be fixed like report for march 2017 – Rahul Pansare Mar 03 '17 at 09:35
  • This might be of some help: http://stackoverflow.com/q/13985470/2298301 – Dhruv Saxena Mar 03 '17 at 09:54
  • 1
    The accepted answer on the duplicate link describes both static (columns are known in advance) and dynamic (columns are not known in advance) pivoting in MySQL. It may be more efficient to do such transformations in the application and not in MySQL. – Shadow Mar 03 '17 at 10:00

1 Answers1

0
CREATE TABLE mp_attendence (atid int, atdisplayname varchar(9), atroll varchar(9), atstatus varchar(9), atdate date, atbatch varchar(9)) ;

INSERT INTO mp_attendence (atid, atdisplayname, atroll, atstatus, atdate, atbatch) VALUES (1, 'lavkush', '1', 'absent', '2017-03-01', 'p1'), (2, 'dhanji', '2', 'present', '2017-03-01', 'p1'), (3, 'lavkush', '1', 'present', '2017-03-02', 'p1'), (4, 'dhanji', '2', 'present', '2017-03-02', 'p1'), (5, 'lavkush', '1', 'present', '2017-03-04', 'p1'), (6, 'dhanji', '2', 'absent', '2017-03-04', 'p1'), (7, 'lavkush', '1', 'present', '2017-03-05', 'p1'), (8, 'dhanji', '2', 'absent', '2017-03-05', 'p1') ;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
  '(case when atstatus = ''',
  atstatus,
  ''' then (atstatus) end) AS ',
   replace (DATE_FORMAT(atdate, '%d %M %Y'), ' ', '')
)
) INTO @sql
from mp_attendence;


SET @sql = CONCAT('SELECT atdisplayname, atroll, ', @sql, ' from mp_attendence
group by atroll');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

From above query i achieve this result but i want to combine same dates like 1st march result should be in same row.

result achieved

Rahul Pansare
  • 1
  • 2