Convert Rows to Columns in MySQL Dynamically

Question

I am having an issue with MySQL. I want to have dynamic columns based on the rows. Here are the details

SELECT `marks`.`id` , `marks`.`studentID` , `marks`.`subjectID` , `marks`.`mark`
FROM `Mark` `marks`
LEFT OUTER JOIN `Student` `students` ON ( `students`.`id` = `marks`.`studentID` )
WHERE (
`students`.`classID` =1
)
LIMIT 0 , 30

My Output is 
+----+-----------+-----------+------+
| id | studentID | subjectID | mark |
+----+-----------+-----------+------+
|  1 |         1 |         1 |   20 |
|  2 |         1 |         2 |   36 |
|  3 |         2 |         1 |   47 |
|  4 |         2 |         2 |   43 |
+----+-----------+-----------+------+
4 rows in set (0.00 sec)


Output I need is 

+----+-----------+-----------+-----------+
| id | studentID | subject_1 | subject_2 |
+----+-----------+-----------+-----------+
|  1 |         1 |        20 |        36 |
|  2 |         2 |        47 |        43 |
+----+-----------+-----------+-----------+
4 rows in set (0.00 sec)

No of subjects can very depending on the enteries in the Subject table. I need only one row per user showing all the marks. Here are the table structure I use.

--
-- Table structure for table `Mark`
--

CREATE TABLE IF NOT EXISTS `Mark` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentID` int(11) NOT NULL,
  `subjectID` int(11) NOT NULL,
  `mark` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

--
-- Table structure for table `Student`
--

CREATE TABLE IF NOT EXISTS `Student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `classID` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

--
-- Table structure for table `Subject`
--

CREATE TABLE IF NOT EXISTS `Subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Thanks is advance.

Answer 1

You can't have dynamic columns, at least without dynamically generating the SQL. You can build the SQL in a stored procedure as per this answer

MySQL pivot table query with dynamic columns

Alternatively it might be simpler to do this in your application code by selecting the distinct subjects in one query and using that result set to build the SQL that retrieves the result set you are after. At least with the logic in the application code you have some idea of how many columns you will be seeing in the result set.

Answer 2

try this sqlFiddle demo

SELECT `marks`.`studentID` ,
  GROUP_CONCAT(if(`marks`.`subjectID`=1,`marks`.`mark`,NULL)) AS subject_1,
  GROUP_CONCAT(if(`marks`.`subjectID`=2,`marks`.`mark`,NULL)) AS subject_2
FROM `Mark` `marks`
LEFT OUTER JOIN `Student` `students` ON ( `students`.`id` = `marks`.`studentID` )
WHERE (
`students`.`classID` =1
)
GROUP BY `marks`.`studentID`

if there are more subjects just add more GROUP_CONCAT lines with a different subjectID

below is an example using a stored procedure and DYNAMICALLY build the query using a cursor sqlFiddle

DROP PROCEDURE IF EXISTS  getMarks//
CREATE PROCEDURE getMarks (IN INclassID INT)
BEGIN
  -- First we declare all the variables we will need
  DECLARE loopSubjectId INT;
  DECLARE dynamicSql VARCHAR(5000);
  DECLARE finalSql VARCHAR(5000);
  -- flag which will be set to true, when cursor reaches end of table
  DECLARE exit_loop BOOLEAN;         

  -- Declare the sql for the cursor
  DECLARE example_cursor CURSOR FOR
    SELECT DISTINCT subjectID
    FROM Student s, Mark m
    WHERE s.id = m.studentID
    AND s.classID = INclassID;

  -- Let mysql set exit_loop to true, if there are no more rows to iterate
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
  SET dynamicSql = '';
  SET finalSql = '';
  -- open the cursor
  OPEN example_cursor;

  -- marks the beginning of the loop
  example_loop: LOOP

    -- read the name from next row into the variable l_name
    FETCH  example_cursor INTO loopSubjectId;
    -- check if the exit_loop flag has been set by mysql, 
    -- if it has been set we close the cursor and exit 
    -- the loop
    IF exit_loop THEN
        CLOSE example_cursor;
        LEAVE example_loop;
    END IF;
    SET DynamicSql = CONCAT(DynamicSql,',GROUP_CONCAT(if(marks.subjectID=',loopSubjectId,',marks.mark,NULL)) AS subject_',loopSubjectId);
  END LOOP example_loop;
  SET finalSql = CONCAT('SELECT students.name,marks.studentID',DynamicSql,
                         ' FROM Mark marks
                           LEFT OUTER JOIN Student students ON (students.id = marks.studentID)
                           WHERE (students.classID=?)
                           GROUP BY marks.studentID');
  -- now we run set some variables and run the dynamically built query
  SET @finalSql = finalSql;
  SET @INClassId = INClassID;
  PREPARE stmt1 FROM @finalSql;
  EXECUTE stmt1 USING @INclassID;
END//

this code structure is found from this example

Answer 3

According to you desired output, I' assuming there are 2 rows for each studentID and each of 2 rows has subjectID 1 or 2.

Could you try this?

Using JOIN

SELECT t1.studentID, t1.mark AS subject_1, t2.mark AS subject_2
FROM
(
    SELECT `marks`.`studentID` , `marks`.mark
    FROM `Mark` as `marks`
    LEFT OUTER JOIN `Student` AS `students`
        ON ( `students`.`id` = `marks`.`studentID` AND subjectID = 1)
    WHERE `students`.`classID` =1
) t1 INNER JOIN 
(

    SELECT `marks`.`studentID` , `marks`.mark
    FROM `Mark` as `marks`
    LEFT OUTER JOIN `Student` AS `students`
        ON ( `students`.`id` = `marks`.`studentID` AND subjectID = 2)
    WHERE `students`.`classID` =1
) t2 ON t1.studentID = t2.studentID;

Using CROSS TABULATION

SELECT `marks`.`studentID`, 
    SUM(IF(subjectID = 1, mark, 0)) AS subject_1,
    SUM(IF(subjectID = 2, 0, mark)) AS subject_2
FROM `Mark` as `marks`
LEFT OUTER JOIN `Student` AS `students`
    ON ( `students`.`id` = `marks`.`studentID`)
WHERE `students`.`classID` =1
GROUP BY marks.studentID

JOIN and CROSS TABULATION are general form to convert vertical result to horizontal (with my restricted knowledge)