Mysql table column to row

Question

i am in difficulties in doing following table (mysql). i have stdent-id,questions,answers and questions-id of a table. below given, is there any way to output table something below output table?

My MySql query following

SELECT a.`person_id` AS STUDENT_ID,q.`questions`,a.`answer` as ANS,q.`question_id`
FROM
questions q
left join answer a on a.`question_id`=q.`question_id`
left join table_info t on q.`question_id`=t.`question_id`

after this query table look like this

 student_id Question Answer Question_Id Ans_id
------------------------------------------------------------
 1 Name Jonathan 1 1
 1 Age 12 2 2
 1 Sex Male 3 3
 1 Area LA 4 4
 2 Name Mary 1 5
 2 Age 11 2 6
 2 Sex Female 3 7
 2 Area NY 4 8
 3 Name David 1 9
 3 Age 12 2 10
 3 Sex Male 3 11
 3 Area NY 4 12

I need output like following

Student_Id Name Age Sex Area
-----------------------------------------------
1 Jonathan 12 Male La
2 Mary 11 Female NY
3 David 12 Male NY

Questions table:

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `questions` varchar(250) DEFAULT NULL,
 `questions_cat` int(11) DEFAULT NULL COMMENT 'which category falls this questions. when load questions, then we can load questions by category',
 `question_order` int(11) DEFAULT NULL COMMENT 'sequence/order of the Q',
 `questions_status` varchar(45) DEFAULT NULL,
 `questions_type_id` int(11) NOT NULL,
 `text_question_top` varchar(200) DEFAULT NULL,
 `text_question_bottom` varchar(200) DEFAULT NULL,
 `input_class` varchar(100) DEFAULT NULL,
 `input_id` varchar(50) DEFAULT NULL COMMENT 'eg id="idName"',
 `input_placeholder` varchar(50) DEFAULT NULL COMMENT 'PlaceHolder',
 `input_onchange` varchar(200) DEFAULT NULL COMMENT 'Onchange, like JS',
 `input_other` varchar(200) DEFAULT NULL COMMENT 'text only, eg ng-click="callFunction"',
 `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `update_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 `update_by` int(11) DEFAULT NULL,
 `view_text` varchar(200) DEFAULT NULL,
 PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

Answer table:

CREATE TABLE `answer` (
 `answer_id` int(11) NOT NULL AUTO_INCREMENT,
 `answer` varchar(300) DEFAULT NULL,
 `question_id` int(11) NOT NULL,
 `person_id` int(11) NOT NULL,
 `answer_other` varchar(100) DEFAULT NULL,
 `created_by` int(11) NOT NULL,
 `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 `update_by` int(11) DEFAULT NULL,
 PRIMARY KEY (`answer_id`,`question_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8

table_info table:

CREATE TABLE `table_info` (
 `tableInfo_id` int(11) NOT NULL AUTO_INCREMENT,
 `table_link` varchar(200) NOT NULL,
 `function` varchar(100) DEFAULT NULL,
 `question_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`tableInfo_id`),
 KEY `fk_tableInfo_Questions` (`question_id`),
 CONSTRAINT `fk_tableInfo_Questions` FOREIGN KEY (`question_id`) REFERENCES `questions` (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

• joanolo: Please, add your table data as text, so that it can be copied easily by people trying to give you a response.
• til_b: This does not help your problem, but the column names "question" and "answer" are very wrong. Question should probably be renamed to "attribute" and Answer to "value".

Answer 1

SELECT Student_Id,
 ( SELECT Answer FROM t
 WHERE student_id = x.Student_Id
 AND Question = 'Name' ) AS Name,
 ( SELECT Answer FROM t
 WHERE student_id = x.Student_Id
 AND Question = 'Age' ) AS Age,
 ( SELECT Answer FROM t
 WHERE student_id = x.Student_Id
 AND Question = 'Sex' ) AS Sex,
 ( SELECT Answer FROM t
 WHERE student_id = x.Student_Id
 AND Question = 'Area' ) AS Area
 FROM ( SELECT DISTINCT Student_Id FROM t ) AS u
 ORDER BY Student_Id;

Performance would benefit from

INDEX(Student_Id, Question, Answer)

• Shimul Chowdhury: i have added my mysql code, would you please help on it ?
• Rick James: We need to see SHOW CREATE TABLE for the tables in use ! My answer assumed (wrongly) that your first described a single table with the data.
• Shimul Chowdhury: table information given, please try if you can solve this problem, and if you can do this dynamically then it would be great because my questions get change or add or del any time.