Aditya Vikas Devarapalli Aditya Vikas Devarapalli - 1 month ago 6
SQL Question

Creating columns of a table using the rows of another

I'm working on my project in php [handling students attendance system]. I have a list of students along with their unique id[

jntuno
] and I need to create a database in mysql for storing the daily attendance of each student for each subject. So I created my tables in this way :

I have a table
students
in mysql with the following fields and data in it :

my table contents

now I want to create a new table with the each of the values in the
jntuno
field as a columns of my new table.

I want my new table [let us name it
attendance
] to have columns like this :

+------------+-----------+----------+-----------+-----------+
|11341A0501 |11341A0502 |11341A0503|11341A0504 |11341A0505 |......
+------------+-----------+----------+-----------+-----------+
| | | | | |


How to do this in mysql ?

I will later add 3 fields to the
attendance
table namely :

->
date
[the date on which a particular subject is taught] ,

->
subject
[the name of the subject taught] and

->
hours taught
[the number of hours for which a particular subject is taught(can be 1 or 2 or 3 ... upto 6)]

every
subject
taught on a particular
date
will be adding a new
row
to the
attendance
table

Example:

+------------+-----------+-----------------+------------+-----------+----------+-----------+-----------+
|date |subject | classes taught |11341A0501 |11341A0502 |11341A0503|11341A0504 |11341A0505 |..
+------------+-----------+-----------------+------------+-----------+----------+-----------+-----------+
|2013-09-31 |OOPS |3 |2 |3 |0 |1 |3 |


I choose the tables in this way so that the entry of attendance into the table would be more faster.

But many call this a BAD DATABASE STRUCTURE . So please suggest me if there's some other good and efficient database design for my problem

hol hol
Answer

Create the new table with the following statements:

select @s:=concat('create table students_col (',group_concat(jntunno,' CHAR(10)' order by slno),')') from students;
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

observe how the CREATE TABLE is constructed using the group_concat

Demo: SQL Fiddle

In case you also want to insert the names, this is the statement to it:

select @s:=concat('insert into students_col values (',group_concat(concat('"',name,'"') order by slno),')') from students;
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
select * from students_col;

Here is my whole trail:

mysql> drop table if exists students;
Query OK, 0 rows affected (0.00 sec)

mysql> create table students (slno integer, jntunno char(10), name varchar(50));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into students values (1,'1134A0501','ADARI GOPI');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (2,'1134A0502','BALU');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (3,'1134A0503','GEETHA');
Query OK, 1 row affected (0.00 sec)

mysql> drop table if exists students_col;
Query OK, 0 rows affected (0.00 sec)

mysql> select @s:=concat('create table students_col (',group_concat(jntunno,' CHAR(10)' order by slno),')') from students;
+-----------------------------------------------------------------------------------------------+
| @s:=concat('create table students_col (',group_concat(jntunno,' CHAR(10)' order by slno),')') |
+-----------------------------------------------------------------------------------------------+
| create table students_col (1134A0501 CHAR(10),1134A0502 CHAR(10),1134A0503 CHAR(10))          |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Query OK, 0 rows affected (0.21 sec)

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select @s:=concat('insert into students_col values (',group_concat(concat('"',name,'"') order by slno),')') from students;
+------------------------------------------------------------------------------------------------------+
| @s:=concat('insert into students_col values (',group_concat(concat('"',name,'"') order by slno),')') |
+------------------------------------------------------------------------------------------------------+
| insert into students_col values ("ADARI GOPI","BALU","GEETHA")                                       |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Query OK, 1 row affected (0.00 sec)

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from students_col;
+------------+-----------+-----------+
| 1134A0501  | 1134A0502 | 1134A0503 |
+------------+-----------+-----------+
| ADARI GOPI | BALU      | GEETHA    |
+------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> 
Comments