Column to Row and Vice Versa 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:
What you can do is to first unpivot your second table and then use conditional aggregation to pivot it again 

SELECT c,
       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
  FROM
(  
  SELECT t1.column_1,
         t2.c_id,
         c,
         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
 GROUP BY c


Output:


|   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