Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have one table which looks like this in mysql

column1  column2    name    column3 data
1          1        foo     1       1.5
1          1        foo     2       2.5
1          1        foo     3       3.5
1          1        foo     4       4.5
1          1        foo2    1       1.1
1          1        foo2    2       3.5
1          1        foo3    3       5.6
1          1        foo3    4       6.7

I want to do query like something like this, I really don't know how to write as I am new to mysql

select name from mytable where name = 'foo' or name = 'foo2'or name = 'foo3' 

I expect output to be like this

column1 column2 column3    foo    foo2  foo3
1       1         1        1.5     1.1  NaN
1       1         2        2.5     3.5  NaN
1       1         3        3.5     NaN  5.6
1       1         4        4.5     NaN  6.7

Is it possible in mysql ?

share|improve this question
    
This is called a pivot table. See: stackoverflow.com/questions/7674786/mysql-pivot-table –  Digital Chris Mar 20 at 18:20

2 Answers 2

up vote 1 down vote accepted
SELECT column1,column2,column3
MAX(CASE WHEN name='foo' THEN data ELSE 0 END)as foo,
MAX(CASE WHEN name='foo2' THEN data ELSE 0 END)as foo2,
MAX(CASE WHEN name='foo3' THEN data ELSE 0 END)as foo3
FROM tableName
GROUP BY column3
share|improve this answer
    
Thank you so much its working, I wanted to vote but no reputation, so am just accepting please don't mind, can you explain case part ? –  Peter Mar 21 at 15:12
    
Case its like an if,if name=some value then choose data column corresponding to that value.This is called a pivot query. –  Mihai Mar 21 at 17:49
    
Thank you so much Mihai –  Peter Mar 21 at 17:52
    
@Mihai its ok to update the answer to sure it is right :) and user will have the right answer ,nothing needed –  M Khalid Junaid Mar 21 at 20:14

Try this Fiddle

SELECT col1,col2,col3,
MAX(CASE WHEN NAME='foo' THEN DATA ELSE 0 END)AS foo,
MAX(CASE WHEN NAME='foo2' THEN DATA ELSE 0 END)AS foo2,
MAX(CASE WHEN NAME='foo3' THEN DATA ELSE 0 END)AS foo3
FROM Table2
GROUP BY col3
share|improve this answer
    
Thank you so much its working too –  Peter Mar 21 at 15:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.