Pivot a table using `group_concat` and `group by`

Question

I have a table about questions and answers with two related columns with ID's. The answers to the questions can be numeric or alphanumeric like this:

╔══════════╦═════════════╦═══════════╦═════════╗
║ visit_id ║ visit_child ║ question  ║ answer  ║
╠══════════╬═════════════╬═══════════╬═════════╣
║   121340 ║      104280 ║ How much? ║ 3       ║
║   121340 ║      104280 ║ How many? ║ 2       ║
║   121340 ║      104280 ║ Why?      ║ Because ║
║   121340 ║      104280 ║ Really?   ║ Yup     ║
║   121485 ║      114190 ║ How much? ║ 5       ║
║   121485 ║      114190 ║ How many? ║ 6       ║
║   121485 ║      114190 ║ Why?      ║ Why not ║
║   121485 ║      114190 ║ Really?   ║ Sure    ║
╚══════════╩═════════════╩═══════════╩═════════╝

I want to pivot this info so the questions become the columns, and the given answers are placed properly. The result I'm looking for looks like this:

╔══════════╦═════════════╦═══════════╦═══════════╦═════════╦═════════╗
║ visit_id ║ visit_child ║ How much? ║ How many? ║  Why?   ║ Really? ║
╠══════════╬═════════════╬═══════════╬═══════════╬═════════╬═════════╣
║   121340 ║      104280 ║         3 ║         2 ║ Because ║ Yup     ║
║   121485 ║      114190 ║         5 ║         6 ║ Why not ║ Sure    ║
╚══════════╩═════════════╩═══════════╩═══════════╩═════════╩═════════╝

I've made my homework and read several Howto pivot in MySQL tutorials and I've come with this:

SELECT
    infoBase.visit_id,
    infoBase.visit_child,
    GROUP_CONCAT(DISTINCT
        CONCAT('MAX(IF(infoBase.question = \'',question,'\', \'',answer,'\', 0)) AS \'',question,'\'',''), "\n"
    )
FROM    
  visits vi,
  infoBase
WHERE
  vi.id = infoBase.visit_child
GROUP BY
  infoBase.visit_id, infoBase.visit_child

What I get is the following:

╔══════════╦═════════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ visit_id ║ visit_child ║                                                               GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(infoBase.question = \'',question,'\', \'',answer,'\', 0)) AS \'',question,'\'',''), "\n")                                                               ║
╠══════════╬═════════════╬════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║   121340 ║      104280 ║ MAX(IF(infoBase.question = 'How much?', '3', 0)) AS 'How much?',MAX(IF(infoBase.question = 'How many?', '2', 0)) AS 'How many?',MAX(IF(infoBase.question = 'Why?', 'Because', 0)) AS 'Why?',MAX(IF(infoBase.question = 'Really?', 'Yup', 0)) AS 'Really?'  ║
║   121485 ║      114190 ║ MAX(IF(infoBase.question = 'How much?', '5', 0)) AS 'How much?',MAX(IF(infoBase.question = 'How many?', '6', 0)) AS 'How many?',MAX(IF(infoBase.question = 'Why?', 'Why not', 0)) AS 'Why?',MAX(IF(infoBase.question = 'Really?', 'Sure', 0)) AS 'Really?' ║
╚══════════╩═════════════╩════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

The results retrieved by group_concat are correct but I'd like to, let's say "convert" that string to real columns. This has to be done as dynamically as possible, because in the real table I don't know the exact number of questions and what are they, so I can't hardcode the questions in many group_concat. What am I doing wrong??

THANKS!

P.S. This in an over simplification of a greater query. If more info needs to be added when new problems arise, I'll add it.

Possible duplicate of [MySQL pivot table query with dynamic columns](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) — shmosel, Apr 20 '17 at 22:00
@shmosel Seems similar yes, I haven't seen the question before. I'll try to understand the answers there in the meanwhile. Thanks. — Metafaniel, Apr 20 '17 at 22:07
[_Here's_](http://mysql.rjweb.org/doc.php/pivot) how to dynamically generate the query. Put that in a stored proc, then prepare & execute it. `CALLing` that proc will do it all in "one step". — Rick James, Apr 21 '17 at 05:48

score 2 · Answer 1 · answered Apr 20 '17 at 21:53

2

try a query like this. its not tested !

SELECT
    infoBase.visit_id,
    infoBase.visit_child,
    GROUP_CONCAT(IF(question = 'How much?', answer ,'') SEPARATOR '' ) AS 'How much?' ,
    GROUP_CONCAT(IF(question = 'How many?', answer ,'') SEPARATOR '' ) AS 'How many?' ,
    GROUP_CONCAT(IF(question = 'Why?',      answer ,'') SEPARATOR '' ) AS 'Why?' ,
    GROUP_CONCAT(IF(question = 'Really?',   answer ,'') SEPARATOR '' ) AS 'Really?'
    )
FROM    
  visits vi,
  infoBase
WHERE
  vi.id = infoBase.visit_child
GROUP BY
  infoBase.visit_id;

answered Apr 20 '17 at 21:53

Bernd Buffen

14,525
2
24
39

OK I understand, but I'm trying to do this as dynamically as possible because in the real table I don't know how many questions are there and what are they. So I can't hardcode the questions. I've updated my question accordingly. Thanks – Metafaniel Apr 20 '17 at 21:57
I see you use many `group_concat`, one for each column. Is there a way to emulate this dynamically? Using a loop or something? – Metafaniel Apr 20 '17 at 22:00
@Metafaniel if you're using some coding language like Java you could iterate through the rows to create the SQL dynamically using something like JDBC. – wheeleruniverse Jan 16 '19 at 13:23
@BerndBuffen Thanks this is exactly what I was looking for. – wheeleruniverse Jan 16 '19 at 13:23

Pivot a table using `group_concat` and `group by`

1 Answers1