Swithcing rows and columns based on a particular column in mysql

Question

I have a table in MySQL which looks as below

**row      name     value**
 1         name1    value1
 1         name2    value2
 1         name3    value3
 2         name1    value4
 2         name2    value5
 2         name4    value6
 3         name1    value7
 3         name2    value8
 3         name3    value9

I want to return the data in the following format.

**row      name1     name2     name3      name4**
  1        value1    value2    value3     nil
  2        value4    value5    nil        value6
  3        value7    value8    value9     nil

or I want a json which looks like:

{
 {
  name1:value1,
  name2:value2,
  name3:value3,
 },
 {
  name1:value4,
  name2:value5,
  name4:value6,
 },
 {
  name1:value7,
  name2:value8,
  name3:value9,
 },
}

What can be the query? Is there any other method to achieve this in ruby?

Answer 1

For the first option, you can try the following pivot query:

SELECT row,
       MAX(CASE WHEN name ='name1' THEN value END) AS name1,
       MAX(CASE WHEN name ='name2' THEN value END) AS name2,
       MAX(CASE WHEN name ='name3' THEN value END) AS name3,
       MAX(CASE WHEN name ='name4' THEN value END) AS name4
FROM yourTable
GROUP BY row

Answer 2

select row,
       max(case when name ='name1' then value end)  as name1,
       max(case when name ='name2' then value end)  as name2,
       max(case when name ='name3' then value end)  as name3,
       max(case when name ='name4' then value end ) as name4
from table_name 
group by row