How change colums for rows in a query

Question

I have a survey with many objectives for assess from 1 to 5 in my web page. I send the survey to my database in my table of survey. For example

 obj1value | obj2value | obj3value ...  | obj17value |

I have a value for every survey.

Now I need to do some charts for present a report of the survey. I need to add up the totals for each objective. I have this query

SELECT SUM(obj1_value) AS objective1, SUM(obj2_value) AS objective2, SUM(obj3_value) AS objective3, FROM tbl_survey

I have this till objective17 and the query returns

| objective1 | objective2 | objective3 | 
|   ---      |    ---     |    ---     |
|    17      |     12     |    5       |

But I need to change the query like this

_objective_ | _Value_           
objective1  | 17 
objective2  | 12 
objective3  | 5  
Objective17 |  n

Someone can help me with the query to optimize because in this way I can convert the table php in json and took a bar chart library so easy.
Thank you

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — zambonee, Sep 11 '17 at 18:27

score 0 · Answer 1 · answered Sep 11 '17 at 18:55

0

the solution for this problem is PIVOT AN UNPOVIT , and you can change the out put us you want , this is tutorial can help you

answered Sep 11 '17 at 18:55

Імад Мосбах

51
9

score 0 · Accepted Answer · answered Sep 11 '17 at 19:05

It is probably easiest to do the transformation in PHP. One method in MySQL is a simple UNION ALL:

SELECT 'obj1_value', SUM(obj1_value) 
FROM tbl_survey
UNION ALL
SELECT 'obj2_value', SUM(obj2_value) 
FROM tbl_survey
UNION ALL
. . .

The downside to this approach is that the table is scanned 17 times, so it will probably take about 17 times as long.

You can improve performance by doing an unpivot. Here is one method in MySQL:

SELECT o.obj,
       (CASE WHEN obj = 'objective1' THEN objective1
             WHEN obj = 'objective2' THEN objective2
             . . .
        END) as value
FROM (SELECT SUM(obj1_value) AS objective1, SUM(obj2_value) AS objective2, 
             SUM(obj3_value) AS objective3, . . .
      FROM tbl_survey
     ) s CROSS JOIN
     (SELECT 'objective1' as obj UNION ALL
      SELECT 'objective2' as obj UNION ALL
      . . .
     ) o;

The first option works perfect, but I will try the second, because I need save the result set in one call, not in 17 times. Thanks — Chris Ortiz, Sep 12 '17 at 16:52

How change colums for rows in a query

2 Answers2