I need to create a new table by combining the contents of two different tables. Is there any way to transform columns into rows and rows into columns in MySQL?

I tried using JOIN but I can't seem to transform a column into a row. Please refer to the image below for the task I need to do:

enter image description here

  • What you're looking for is called a pivot; which is not easy with MySQL. I would recommend that you search Stack Overflow for similar issues and start there.
    – Kermit
    Jan 27 '14 at 2:30
  • Create a fiddle and I`ll have a go at it.
    – Mihai
    Jan 27 '14 at 10:25

What you can do is to first unpivot your second table and then use conditional aggregation to pivot it again

       MAX(CASE WHEN column_1 = 'xdata1' THEN value END) xdata1,
       MAX(CASE WHEN column_1 = 'xdata2' THEN value END) xdata2,
       MAX(CASE WHEN column_1 = 'xdata3' THEN value END) xdata3
  SELECT t1.column_1,
         CASE c 
           WHEN 'c_1' THEN c_1
           WHEN 'c_2' THEN c_2
           WHEN 'c_3' THEN c_3
           WHEN 'c_4' THEN c_4
         END value
    FROM table1 t1 JOIN table2 t2
      ON t1.column_id = t2.c_id CROSS JOIN 
    SELECT 'c_1' c UNION ALL
    SELECT 'c_2'   UNION ALL
    SELECT 'c_3'   UNION ALL
    SELECT 'c_4'
  ) n
) q


|   C |   XDATA1 |   XDATA2 |   XDATA3 |
| c_1 | a_data_1 | a_data_2 | a_data_3 |
| c_2 | b_data_1 | b_data_2 | b_data_3 |
| c_3 | c_data_1 | c_data_2 | c_data_3 |
| c_4 | d_data_1 | d_data_2 | d_data_3 |

Here is SQLFiddle demo

  • Did it help? Do you need more help with your question?
    – peterm
    Jan 29 '14 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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