SQL query for incrementing values of row data through sequencing

Question

I have row and column locations of several students. Assuming number of rows and columns are fixed (to 3x3), how can I have a query result listing all row and column combinations, with students mapped to the correct location?

For example given these students data:

Student  Row     Column
Paul     1       1
Chris    1       3
James    2       2
Dwayne   3       3

How to have a query output like this:

Student  Row  Column
Paul     1       1
NULL     1       2
Chris    1       3
NULL     2       1
James    2       2
NULL     2       3
NULL     3       1
NULL     3       2
Dwayne   3       3

Please help! Thank you very much in advance.

Can you please add table schema, I didn't finished to understand what you want, where is the relation between row and colum columns with oyu expecting output... . — Juan Ruiz de Castilla
– Juan Ruiz de Castilla, Commented Dec 13, 2015 at 10:45
@Muhammad Muazzam The output shows that the column header is incrementing until it reaches 3 then the row header increments to 2 and so on depending on the current table. — Raven
– Raven, Commented Dec 13, 2015 at 10:45
@Juan Ruiz de Castilla The column header must increment until it reaches 3 then the row header will now increment and so on. I just want to add rows that have a row and column header values even if the student column contains empty fields. — Raven
– Raven, Commented Dec 13, 2015 at 10:47

Roy Doron · Accepted Answer · 2015-12-13 09:55:10Z

1

While using PHP, Try mysql_insert_id() for your Code.

See Example here: http://php.net/manual/en/function.mysql-insert-id.php

Good luck.

answered Dec 13, 2015 at 9:55

Roy Doron

6051 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Ruiz de Castilla · Accepted Answer · 2015-12-13 12:46:34Z

First of all, you need to know that Mysql haven't a implicit generator of N numbers of rows, like other RDBMS have, but you can emulate this using something like this:

http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

Take a look for study porpuse.

But for a first approach to resolve your problem, you can try this:

SELECT IFNULL((SELECT STUDENT FROM StudentSeatPlan B WHERE B.ROW =  TB.ROW_ AND B.COLUMN = TB.COLUMN_),'') AS STUDENT,
TB.ROW_,TB.COLUMN_
FROM (
SELECT 1 ROW_,1 COLUMN_ UNION ALL 
SELECT 1,2  UNION ALL 
SELECT 1,3  UNION ALL 
SELECT 2,1  UNION ALL 
SELECT 2,2  UNION ALL 
SELECT 2,3  UNION ALL
SELECT 3,1  UNION ALL 
SELECT 3,2  UNION ALL 
SELECT 3,3) TB

Whatever, it seems like you have a schema problem, something wrong it happens that you need generate data in this form in Mysql, maybe you prefered make it in your app if is the case.

Collectives™ on Stack Overflow

SQL query for incrementing values of row data through sequencing

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related