mysql combine rows into columns

Question

Let's say I have a table with the following information (there could be 40+ unique Measures)

table1

id    name    state    Measure       score
1     Joe     CA       work ethic    40
1     Joe     CA       cleanliness   80
1     Joe     CA       helpfulness   70
2     John    TX       work ethic    70
2     John    TX       helpfulness   50
3     Jack    AZ       helpfulness   50

and I would like to combine everyone's measures into separate columns and make the id unique into a new table that would look something like:

table2

id    name    state    workEthicScore    cleanlinessScore    helpfulnessScore
1     Joe     CA       40                80                  70
2     John    TX       70                null                50
3     Jack    AZ       null              null                50

So, ideally I would like to be able to create this new table without manually typing in all the distinct Measures. How would I go about this using Java and MYSQL? I don't want to use mysql group_concat as I would like to have these as separate columns and not combined into a single column.

This question has been asked *numerous* times, and you couldn't find *any* similar questions? Try including the word "pivot" in your search. — spencer7593, Jul 02 '15 at 23:01
http://stackoverflow.com/questions/9685601/transpose-mysql-query-rows-into-columns — spencer7593, Jul 02 '15 at 23:02
Besides that you would probably be better off with a view so that it essentially is a saved query which would give you the results you want each time — MiltoxBeyond, Jul 02 '15 at 23:04
I would try a pivot table, a good example is found here. http://stackoverflow.com/questions/7674786/mysql-pivot-table — Brandon Wilson, Jul 02 '15 at 23:19
mesaure column can have maximum three distinct values or multiple values ?. — Hitesh Mundra, Jul 03 '15 at 03:55

spencer7593 · Accepted Answer · 2015-07-02T23:22:50.387

Start building your query like this:

  SELECT t.id
       , t.name 
       , t.state
       , 0           AS workEthicScore
       , 0           AS cleanlinessScore
       , 0           AS helpfulnessScore
    FROM mytable t
   GROUP
      BY t.id
       , t.name 
       , t.state

To get the values of score returned, you can use an expression that does conditional aggregation, and replace the literal 0 in the statement above with an expression like this:

       , MAX(IF(t.measure = 'work ethic', t.score,NULL)) AS workEthicScore

Repeat that same pattern for the other values of measure.

For a query like this to work, the number of columns, and the names of the columns, and the expressions used to return a value for the column must be specified in the SQL statement. This cannot be done dynamically within the statement.

To get a result like this dynamically requires two separate statements. The second statement has to be of a form like shown above. You can use a separate statement to get some values to help you build that statement, e.g.

  SELECT m.measure FROM mytable m GROUP BY m.measure

or, you could do something fancier, to generate the expression and alias to include in the SELECT list of the second statement:

  SELECT CONCAT('      , MAX(IF(t.measure='''
           ,REPLACE(m.measure,'''','''''')
           ,''',t.score,NULL) AS `'
           ,m.measure
           ,'`'
         ) AS expr
    FROM mytable m
   GROUP BY m.measure

It's not possible to get to get a result like this with dynamic columns, in a single statement.

score 0 · Answer 2 · answered Jul 03 '15 at 04:36

Here is query-

mysql> select id, 
              name, 
              state, 
              max(if(measure='work ethic', score, null)) as `work ethic`, 
              max(if(measure='cleanliness',score, null)) cleanliness,        
              max(if(measure='helpfullness',score, null)) helpfullness 
        from tt 
        group by id;
+------+------+-------+------------+-------------+--------------+
| id   | name | state | work ethic | cleanliness | helpfullness |
+------+------+-------+------------+-------------+--------------+
|    1 | Joe  | CA    |         40 |          80 |           70 |
|    2 | Jhon | TX    |         70 |        NULL |           50 |
|    3 | Jack | AZ    |       NULL |        NULL |           50 |
+------+------+-------+------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql combine rows into columns

2 Answers2