mysql - 查询以选择列名和值以获取具有新标题的新表

Question

我有一个 mysql 表:

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

有没有办法获取这样的数据:

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

我需要查询来获取列名和值。

Answer 1

我已经改编了this answer根据以下假设满足您的需求:1)您正在寻找通用解决方案。 2) 在我的示例中，表的名称是cells。

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;