MySQL Retrieve Rows and Convert them to Columns

Question

I’m using the below query to return the form ids with their attachment ids. Each form can have no attachments, one attachment or two attachments.

SELECT form.id       AS 'Form ID', 
       attachment.id AS 'Attachment ID' 
FROM   form, 
       attachment 
WHERE  form.id = attachment.form_id;

I'm retrieving the results as follows:

+---------+---------------+
| Form ID | Attachment ID |
+---------+---------------+
| 1       | 1             |
| 1       | 2             |
| 2       | 3             |
| 3       | 4             |
| 5       | 5             |
| 5       | 6             |
| 6       | 7             |
+---------+---------------+

I'm trying to figure out a way to retrieving the results as follows:

+---------+-------------------+-------------------+
| Form ID | Attachment ID - 1 | Attachment ID - 2 |
+---------+-------------------+-------------------+
| 1       | 1                 | 2                 |
| 2       | 3                 | NULL              |
| 3       | 4                 | NULL              |
| 4       | NULL              | NULL              |
| 5       | 5                 | 6                 |
| 6       | 7                 | NULL              |
+---------+-------------------+-------------------+

http://stackoverflow.com/questions/27563172/please-anyone-help-about-my-case-in-mysql-query?noredirect=1#comment43552043_27563172.This might be help you. — Charvee Shah, Jan 09 '15 at 13:23
And never more than 2 attachment? Just do a LEFT JOIN with same table, and check attachment id2 > attachment id1. — jarlh, Jan 09 '15 at 13:23
It's never more than 2. How can I check different attachment ids? — AlGallaf, Jan 09 '15 at 13:25

score 2 · Accepted Answer · edited Jan 09 '15 at 14:21

2

Use aggregation and a left join:

SELECT f.id as `Form ID`,
       MIN(a.id) as `Attachment ID - 1` ,
       (CASE WHEN MIN(a.id) <> MAX(a.id) THEN MAX(a.id) END) as `Attachment ID - 2`
FROM   form f left join
       attachment a
       on f.id = a.form_id
GROUP BY f.id;

edited Jan 09 '15 at 14:21

AlGallaf

627
6
15
28

answered Jan 09 '15 at 13:24

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jan 09 '15 at 13:25

Try this:

SELECT FormID, 
       MAX(CASE WHEN AttachmentNo % 2 = 1 THEN AttachmentID ELSE 0 END) AS 'Attachment ID - 1', 
       MAX(CASE WHEN AttachmentNo % 2 = 0 THEN AttachmentID ELSE 0 END) AS 'Attachment ID - 2'
FROM (SELECT f.id AS FormID, a.id AS AttachmentID, 
             IF(@formId = @formId:=f.id, @id:=@id+1, @id:=1) AS AttachmentNo
      FROM form f 
      LEFT JOIN attachment ON f.id = a.form_id, (SELECT @formId:= 0, @id:=0) AS A 
      ORDER BY f.id. a.id
     ) AS A
GROUP BY FormID

MySQL Retrieve Rows and Convert them to Columns

2 Answers2