Creating columns of a table using the rows of another

Question

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 :

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

Answer 1

This procedure will do the work:

DELIMITER ||
DROP PROCEDURE IF EXISTS `test`.`pivot`;
CREATE PROCEDURE `test`.`pivot`()
    MODIFIES SQL DATA
BEGIN
    DROP TABLE IF EXISTS `test`.`new_table`;
    SELECT GROUP_CONCAT(CONCAT(`jntunno`, ' CHAR(10) NOT NULL') SEPARATOR ', ') FROM `test`.`students` INTO @sql;
    SET @sql := CONCAT('CREATE TABLE `test`.`new_table` (', @sql, ') ENGINE=InnoDB;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @sql := NULL;
END;
||
DELIMITER ;

If you cannot use stored procedures, you can easily translate that code into PHP, or any language you use.

Answer 2

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> 

Answer 3

create table new_table
select distinct jntuno from students;

or

create table new_table (jntuno varchar(10));
insert into new_table 
select distinct jntuno from students;

Answer 4

try

create table new_table (jntuno varchar(10),fieldsname varchar(10));
insert into new_table(jntuno,fieldsname)
values(select distinct jntuno from s tudents,'test');

Answer 5

I'm going to try an answer your question of how to better design this DB:

The Explanation: Instead of having a column for each student and I assume a row for each day, you should set the table up to have a row for each student x by each day and you can use foreign keys to enforce data integrity

The main reason for this is generally something like the number of students is not a static number. In order to optimize your data usage in a setup like this you would essentially need to recreate the table every time a student is added or removed. So if you start out with 30 students you then have a table with 30 columns, if then the number of students drops to 15 every time you add a record you are creating 15 unused values and this will gradually take up a lot more storage than you actually need.

Solution: If you structure your attendance table like this:

attendance ID | JNTUNO | Date | Subject | Hours

Attendance id will simply be a unique identifier for the row, I would suggest either a guid or an auto incremented int

JNTUNO would be a foreign key, pointing back to your Student table

This way on the scenario I presented earlier, with 30 students you will add 30 rows every day with 5 values each but when your number of students changes to 15 you add only 15 rows.

I don't know MYSQL syntax very well but if you'd like I can try to piece together a concrete implementation if my explanation isn't clear; just let me know.

Answer 6

declare @s as varchar(8000);
declare @s2 as varchar(25);
 set @s ='create table attendance (';
 declare s1  cursor
 for 
 select jntunno from students  
 OPEN s1 
    fetch next from s1 into @s2 
    set @s = @s +'['+@s2 +'] numeric(4,2)'
    fetch next from s1 into @s2
     WHILE @@FETCH_STATUS = 0
     BEGIN
         set @s = @s +',['+@s2 +'] numeric(4,2)'
         fetch next from s1 into @s2
     end
 CLOSE s1 -- close the cursor
DEALLOCATE s1
set @s = @s+')'
exec(@s)

Answer 7

You don't want to be adding columns every time you add a student.

I'd design it like this:

Table:  Attendance
    Columns:
        Date
        Subject
        ClassNumber
        Missed_jntuno

Then you add a row for each student/class combo that is missed (or, if students miss more than they attend, you might want to flip that to a Attended_jntuno field instead and add the row when they make it to class — or if you're really feeling completest, always write a row for every student and then have another bit/boolean column for attended or not).

One advantage to this way is that you can see which classes got which students in addition to a daily sum. Another is that this is much friendlier to things like OLAP cubes for flexible reporting.

Answer 8

Basic database design is jumping up right now and shouting in your face: "You're doing it wrong!".

What you're trying to do here is put a many to many relationship in 1 table, while it should actually be in 3 tables.

What you should do is keep your student table as-is and add a subjects table with 1 row for each subject, but not including the date.

Then you want to have another table Attendance with a reference column to Student ID, a reference column to subject ID, a Date field and a Presence field.

What you're trying to do right now is trying to store what's basically volatile data in your metadata about your table, which you should never do. For example, what if you have a student join your school 1 week into the grade? then your table suddenly gets an extra column with no values for the first week.

In addition, your system means that database queries are much harder, since you're storing data you want to filter on in your column header. Other people in here have given a number of reasons why this is a bad idea.