1

I have the following tables:

  • Question (id, question)
  • Answer (id, answer, qId, sess)
  • Answer.qId = Question.id,

The output from the query I would like (based on data below), would be:

+----+-----+------+
|sess|Color|Shape | 
+----+-----+------+
|ses1|green|square| 
|ses2|black|square| 
|ses3|black|circle|
+----+-----+------+

The 'sess' (meaning session) being Answer.sess, the other columns should be generated from the Question.question table.

I will not know the values/amount of rows for the 'question' table, and these may change. So I'm not able to hard code a query. I do believe this can be done using prepared statements. I've seen some prepared statements as answers on stackflow for similar questions, but are either one table, or the values are part of the question table, either way I can't seem to crack it!

Example schema/data:

CREATE TABLE answer
(`id` int, `answer` varchar(16), `qId` int, `sess` varchar(15));

INSERT INTO answer
(`id`, `answer`, `qId`, `sess`)
VALUES
(1, 'green', 1, 'ses1'),
(2, 'square', 2, 'ses1'),
(3, 'black', 1, 'ses2'),
(4, 'square', 2, 'ses2'),
(5, 'black', 1, 'ses3'),
(6, 'circle', 2, 'ses3');

CREATE TABLE question
(`id` int, `question` varchar(10));

INSERT INTO question
(`id`, `question`)
VALUES
(1, 'Color'),
(2, 'Shape');
Taryn
  • 242,637
  • 56
  • 362
  • 405
user3550648
  • 21
  • 1
  • 2
    This question should have bluefeet prints all over it. – spencer7593 Jun 24 '15 at 01:12
  • This can be done using MySQL's prepared statements and a sproc, but surely far simpler to handle display logic at the application level/presentation layer assuming that's available. – Strawberry Jun 24 '15 at 04:06
  • Maybe this can help: http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/ – gandra404 Jun 24 '15 at 10:42
  • While this is a duplicate - here is a sql fiddle with some sample queries to help solve it. http://sqlfiddle.com/#!9/c633c/3 – Taryn Jun 24 '15 at 12:48

0 Answers0