Create a table with column names derived from row values of another table

Suppose I have the following table with a single column:

Table_1

-----------
| nameCol |
-----------
| A       |
| A       |
| B       |
| C       |
-----------

And I want to create a new table with the following column names:

Table_2

| pk | A | B | C |

That is, the data from one table become the column names of the second table. There may be a pivot involved at some level, but I'm unable to really get the answer.

I tried:

create table Table_2 (
  select group_concat(distinct(nameCol), " varchar(50), ")
  from Table_1
);

---

You could use a dynamic query:

SELECT
  CONCAT(
    'CREATE TABLE Table_2 (',
    GROUP_CONCAT(DISTINCT
      CONCAT(nameCol, ' VARCHAR(50)')
      SEPARATOR ','),
    ');')
FROM
  Table_1
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.

链接地址: http://www.djcxy.com/p/12612.html

上一篇: 如何使用对齐

下一篇: 创建一个表，其中的列名来自另一个表的行值