CODEWITHSUNDEEP
mysqlsqlselectjoinpivot
AlGallaf
AlGallaf

Reputation: 635

MySQL Retrieve Rows and Convert them to Columns

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              |
+---------+-------------------+-------------------+

Upvotes: 1

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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;

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29051

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

Upvotes: 1

Related Questions