how to transpose the table in Mysql

Question

How to convert columns and primary rows??

tbl1

     |   p1   |   p2   |   p3   | ...
g1   |    1   |   2    |    3   | ...
g2   |    4   |   5    |    6   | ...
g3   |    7   |   8    |    9   | ...

expected result

     |   g1   |   g2   |   g3   | ...
p1   |    1   |   4    |    7   | ...
p2   |    2   |   5    |    8   | ...
p3   |    3   |   6    |    9   | ...

how to make expected result table with tbl1

The search phrase you're looking for is 'pivot table'. See this answer: http://stackoverflow.com/questions/7674786/mysql-pivot-table — S McCrohan, Dec 17 '13 at 03:42

score 0 · Answer 1 · answered Dec 17 '13 at 04:45

This is the concept of PIVOT table in MySql.

Here I have given solution of your problem considering columns P1,P2 and P3.See this SQL fiddle and understand how it works after that you will be able to do it by your own.

SELECT TEMP,
       MAX(CASE WHEN unit = 'G1' THEN value END) `G1`,
       MAX(CASE WHEN unit = 'G2' THEN value END) `G2`,
       MAX(CASE WHEN unit = 'G3' THEN value END) `G3`
  FROM
(
  SELECT unit, TEMP,
         CASE TEMP
            WHEN 'P1' THEN P1
            WHEN 'P2' THEN P2
            WHEN 'P3' THEN P3
         END value
    FROM tbl1 t CROSS JOIN
  (
    SELECT 'P1' TEMP UNION ALL
    SELECT 'P2' UNION ALL
    SELECT 'P3' 
  ) c
) q
 GROUP BY TEMP
 ORDER BY FIELD(TEMP, 'P1', 'P2', 'P3')

how to transpose the table in Mysql

1 Answers1