Query to select column name and value to get a new table with new headers

Question

I have a mysql table:

| Col1 | Col2 | Col3 |   
++++++++++++++++++++++ 
|  3.2 |   2  |  1   |

Is there a way to get data like this:

| Name | Value |
++++++++++++++++
| Col1 |  3.2  |    
| Col2 |   2   |
| Col3 |   1   |

I need query which allow me to get column name and value.

score 1 · Accepted Answer · answered Aug 07 '17 at 03:56

I've adapted this answer to your needs with the following assumptions: 1) You are searching for a generic solution. 2) The name of the table is cells in my example.

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select ''',
      c.column_name,
      ''' as Name, ',
      c.column_name,
      ' as Value 
      from cells'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'cells'
order by c.ordinal_position;


SET @sql 
  = CONCAT('select Name,Value
           from
           (', @sql, ') x  order by Name');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Query to select column name and value to get a new table with new headers

1 Answers1