implementing Some kind of pivot in mysql

Question

I have a table in mysql in this structure

table: member

| Id | Name | Lastname | Username 
| --------------------------------
| 1  | Alexi| Lalas    | alexi
| 2  | Jack | Louis    | louis

And I have a table called member images with this structre:

table: image

| Id | MemberId | Image      | Type  |
|------------------------------------|
| 50 | 1        | face.jpg   |Avetar |
| 51 | 1        | image.jpg  |Gallery|
| 52 | 2        | main.jpg   |Avetar |
| 53 | 2        | jungle.jpg |Gallery|

And I want to get this result

| Id | Name | Lastname | Username | Image1   | Image2   |
|-------------------------------------------------------|
| 1  | Alexi| Lalas    | alexi    |face.jpg  |image.jpg |
| 2  | Jack | Louis    | louis    |main.jpg  |jungle.jpg|

Becuase of some reasons I can't handle is on app side and I have to do it on sql side. Imagin that I always have 2 type of images and we always have Image1 and Image2.

Any help would be appritiated.

The keyword you're after is JOIN: w3schools.com/sql/sql_join.asp — Tasos Bitsios
– Tasos Bitsios, Commented Dec 15, 2013 at 13:18

Mahmoud Gamal · Accepted Answer · 2013-12-15 13:25:16Z

2

Since they are only two types, you can use the CASE expression to do so. Something like this:

SELECT
  m.Id,
  m.Name,
  m.LastName, 
  m.UserName,
  MAX(CASE WHEN i.Type = 'Avetar' THEN i.Image END) AS 'Image1',
  MAX(CASE WHEN i.Type ='Gallery' THEN i.Image END) AS 'Image2'
FROM member AS m
LEFT JOIN image AS i ON m.Id = i.MemberId
GROUP BY m.Id,
         m.Name,
         m.LastName, 
         m.UserName;

Note that: LEFT JOIN will include all the members from the member table even if they have no images in the image table, in this case NULL will be returned.

See it in action here:

SQL Fiddle Demo

answered Dec 15, 2013 at 13:25

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GL_Stephen · Accepted Answer · 2013-12-15 13:20:25Z

0

The way to do this is via a subselect (subquery). You would subselect the first column as the image for the user withe Avetar type and the other column subselect would be for the image with the type Gallery. If you can't find a way to make User ID and Type unique in your sub table then you are going to have issues with this type of query. Keep in mind that a subselects can impact performance heavily.

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

answered Dec 15, 2013 at 13:20

GL_Stephen

5995 silver badges11 bronze badges

Collectives™ on Stack Overflow

implementing Some kind of pivot in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related