'Custom query to retrieve needed data

I am creating an event creation ( where people can attend) system and as creating forms for the events is dynamic ( the number of different inputs and such) I am inserting every attendee to the same table.

The table looks like this:

| id  | event_id | question      | answer          | paid | shown |
| 123 |      174 | enimi         | mati            | NULL | jah   |
| 124 |      174 | pnimi         | kask            | NULL | jah   |
| 125 |      174 | mail          | [email protected]      | NULL | jah   |
| 126 |      174 | enimi         | kati            | NULL | jah   |
| 127 |      174 | pnimi         | kuusk           | NULL | jah   |
| 128 |      174 | mail          | [email protected]     | NULL | jah   |
| 129 |      174 | enimi         | tiina           | NULL | jah   |
| 130 |      174 | pnimi         | lepp            | NULL | jah   |
| 131 |      174 | mail          | [email protected]      | NULL | jah   |
+-----+----------+---------------+-----------------+------+-------+

Is there any way I could retrive this information as:

|event_id|enimi|pnimi|mail        |paid|
|174     |mati |kask | [email protected] |null|
mysql


Solution 1:[1]

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id  INT NOT NULL PRIMARY KEY
,event_id INT NOT NULL
,question      VARCHAR(12) NOT NULL
,answer          VARCHAR(12) NOT NULL
,paid INT NULL
,shown VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(123 ,174 ,'enimi','mati'      ,NULL,'jah'),
(124 ,174 ,'pnimi','kask'      ,NULL,'jah'),
(125 ,174 ,'mail','[email protected]' ,NULL,'jah'),
(126 ,174 ,'enimi','kati'      ,NULL,'jah'),
(127 ,174 ,'pnimi','kuusk'     ,NULL,'jah'),
(128 ,174 ,'mail','[email protected]',NULL,'jah'),
(129 ,174 ,'enimi','tiina'     ,NULL,'jah'),
(130 ,174 ,'pnimi','lepp'      ,NULL,'jah'),
(131 ,174 ,'mail','[email protected]' ,NULL,'jah');

SELECT x.event_id
     , MAX(CASE WHEN x.question = 'enimi' THEN x.answer END) enimi
     , MAX(CASE WHEN x.question = 'pnimi' THEN x.answer END) pnimi
     , MAX(CASE WHEN x.question = 'mail' THEN x.answer END) mail
     , paid
  FROM my_table x
  JOIN 
     ( SELECT event_id
            , question
            , MIN(id) id 
         FROM my_table 
        GROUP 
           BY event_id
            , question
     ) y 
    ON y.id = x.id
 GROUP 
    BY event_id;

+----------+-------+-------+------------+------+
| event_id | enimi | pnimi | mail       | paid |
+----------+-------+-------+------------+------+
|      174 | mati  | kask  | [email protected] | NULL |
+----------+-------+-------+------------+------+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Strawberry

Related Questions