dynamic columns using pivot table query

Question

I have this attendance table

---------------------------------------------------------------------------
attendance_id | stud_id      | week       | sy        |sem         |present
---------------------------------------------------------------------------
1             | 1            | 02/18/2012 | 2010-2011 |1st semester|1
2             | 2            | 02/18/2012 | 2010-2011 |1st semester|1
3             | 3            | 02/18/2012 | 2010-2011 |1st semester|1
4             | 1            | 02/25/2012 | 2010-2011 |1st semester|1
5             | 2            | 02/25/2012 | 2010-2011 |1st semester|1
6             | 1            | 03/03/2012 | 2010-2011 |1st semester|1
7             | 2            | 03/03/2012 | 2010-2011 |1st semester|1
8             | 3            | 03/03/2012 | 2010-2011 |1st semester|1

my query is this

Select cadet_record.fname,cadet_record.lname,cadet_record.mname, student_id,

MAX(case WHEN week = '02/18/2012' then present end) as 'week1',
MAX(case WHEN week = '02/25/2012' then present end) as 'week2'
From attendance
LEFT JOIN cadet_record ON cadet_record.stud_no = attendance.student_id WHERE section = '$section' AND schoolyear = '$year' AND component = '$component' AND semester = '$semester'
GROUP BY student_id

how can I dynamically call all the week without inserting the dates for e.g. 02/28/2012, 02/29/2012 so on and so forth. any ideas? =(

You say "week", but your code shows days? What information are you trying to get from the table? — bfavaretto, Feb 16 '12 at 02:42

Mosty Mostacho · Accepted Answer · 2012-02-16T02:56:24.247

As far as I know, you can't dynamically add columns to a SELECT statement. What you're asking for is a way of presenting data and that is not something MySQL cares about. You should handle that in the front end.

However, you can cheat by creating your queries in your model and dynamically adding those new columns, by dynamically inserting more MAX(case... to the query string. That's not a nice solution, though.

Edit:

Im using php, how can I accomplish that?

So, I guess you're talking about the ugly solution. Well, basically you should dynamically create your query string (pseudocode):

$initialDay = 02/28/2012;
$lastDay = 03/28/2012;
$dayNumber = 1;
$sql = 'Select cadet_record.fname,cadet_record.lname,cadet_record.mname, student_id';
while ($initialDay <= $lastDay) {
    $sql .= ', MAX(case WHEN week = $initialDay then present end) as day' . $dayNumber;
    $initialDay = $initialDay + 1 day;
    $dayNumber++;
}
$sql .= ' From attendance blah blah...';

Then your query should look like this for dates from 02/18/2012 to 03/18/2012:

Select cadet_record.fname,cadet_record.lname,cadet_record.mname, student_id,
,MAX(case WHEN week = '02/18/2012' then present end) as day1
,MAX(case WHEN week = '02/19/2012' then present end) as day2
From attendance
LEFT JOIN cadet_record ON cadet_record.stud_no = attendance.student_id WHERE section = '$section' AND schoolyear = '$year' AND component = '$component' AND semester = '$semester'

GROUP BY student_id

Notice I added days instead of weeks because your example showed increasing days, although the column name was weeks

on your code in this line there's an error `$initialDay = $initialDay + 1 day;` maybe you mean is this? `$date = strtotime(date("Y-m-d", strtotime($date)) . " +1 day");` — Falconerie B Badayos II, Feb 16 '12 at 05:25
That's not PHP it is `pseudocode` :) Of course, the idea is to add a day. I don't know PHP so, I don't know how to add a day — Mosty Mostacho, Feb 16 '12 at 05:29
Have you read my previous comment? :) [Pseudocode](http://en.wikipedia.org/wiki/Pseudocode) — Mosty Mostacho, Feb 16 '12 at 06:30

bfavaretto · Answer 2 · 2012-02-16T03:01:34.250

0

Without pivoting the table, you can get the weekly attendance of each student with this query:

SELECT 
    cadet_record.fname,
    cadet_record.lname,
    cadet_record.mname, 
    student_id,
    week,
    SUM(present) AS att
FROM attendance
LEFT JOIN cadet_record 
    ON cadet_record.stud_no = attendance.student_id 
WHERE section = '$section' 
    AND schoolyear = '$year' 
    AND component = '$component' 
    AND semester = '$semester'
GROUP BY week, student_id
ORDER BY week, cadet_record.fname, student_id

(EDIT Sorry, you should use SUM, not COUNT).

edited Feb 16 '12 at 03:01

answered Feb 16 '12 at 02:55

bfavaretto

71,580
16
111
150

sorry about that, but I have a format to follow see here http://stackoverflow.com/questions/9287413/class-attendance-record-format-sql-query – Falconerie B Badayos II Feb 16 '12 at 03:31

dynamic columns using pivot table query

2 Answers2

Linked