Class attendance record format SQL Query

Question

Possible Duplicate:
MySQL dynamic cross tab

I have student_record table

-------------------------------------------------------------------
student_id | semester    | schoolyear| name  | section |
-------------------------------------------------------------------
1          | 1st Semester| 2011-2012 | john  | c21     |
2          | 1st Semester| 2011-2012 | eric  | c21     |
3          | 1st Semester| 2011-2012 | mark  | c21     |

and attendance table

-------------------------------------------------------------------
attendance_id | stud_id              | week       | 
-------------------------------------------------------------------
1             | 1                    | 02/04/2012 | 
2             | 2                    | 02/04/2012 | 
3             | 3                    | 02/04/2012 | 
4             | 1                    | 02/11/2012 | 
5             | 2                    | 02/11/2012 | 
6             | 1                    | 02/18/2012 | 
7             | 2                    | 02/18/2012 | 
8             | 3                    | 02/18/2012 |

And I want to achieve this output using sql.

-------------------------------------------------------------------
student_id | name    | 02/04/2012 | 02/11/2012  | 02/18/2012 |
-------------------------------------------------------------------
1          | john    | present    | present    | present     |
2          | erik    | present    | absent     | present     |
3          | mark    | present    | present    | present     |

this is very vague to me, can anyone help? I tried this, but didn't work.

Select week,
[02/28/2012],
[02/29/2012]
From attendance
group by student_id

Do you **really** need the result set to be transposed or you are allowed to do that when you prepare output (in your programming language)? — zerkms, Feb 15 '12 at 03:22
You better try in pivot query. Please check there. http://stackoverflow.com/questions/9104483/sql-query-with-return-columns-based-on-grouping/9104641#9104641 — Thit Lwin Oo, Feb 15 '12 at 03:28
You have a tag of PHP on this. I agree with zerkms and would query for the info and then do the formatting in the presentation in PHP. — mikeY, Feb 15 '12 at 03:32

score 0 · Accepted Answer · edited May 23 '17 at 10:34

0

Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".

Here's an example for mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table

Some databases have builtin features for that, see the links below.

SQLServer: http://msdn.microsoft.com/de-de/library/ms177410.aspx

Oracle: http://www.dba-oracle.com/t_pivot_examples.htm

You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set.

Also check this link, you will get your answer provided by MGA...

edited May 23 '17 at 10:34

Community

1
1

answered Feb 15 '12 at 03:43

Fahim Parkar

30,974
45
160
276

The worst thing is that mysql's transposing solutions are based on the fact that you know about the columns characteristics *before* you perform a query. Thus it is suitable for cases when you have fixed amount of columns, while OP doesn't have such – zerkms Feb 15 '12 at 03:49
wow! you are all genius here in stackoverflow! thank you so much for giving that idea. – Falconerie B Badayos II Feb 15 '12 at 03:53
always welcome... happy to help!!! – Fahim Parkar Feb 15 '12 at 03:54
I tried the query on the link you have given me, it didn't work, can you tell where is the error on my sql query found above? – Falconerie B Badayos II Feb 15 '12 at 05:38
try each word... you must be missing something... – Fahim Parkar Feb 15 '12 at 05:40
try to print first for only one date... then go ahead with another dates... – Fahim Parkar Feb 15 '12 at 05:40
proud that you found your problem yourself... Cheers!!! – Fahim Parkar Feb 15 '12 at 06:26
@FahimParkar can you help me with this one? http://stackoverflow.com/questions/9304540/dynamic-columns-using-pivot-table-query – Falconerie B Badayos II Feb 16 '12 at 06:22

Class attendance record format SQL Query

1 Answers1

Linked