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

Question

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
);

score 4 · Accepted Answer · answered Apr 04 '13 at 19:38

4

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.

answered Apr 04 '13 at 19:38

fthiella

48,073
15
90
106

1

Thank you very much for your reply. I tried the above query. However, the query string inside my @sql variable is very long (because there are a large number of columns). This is causing problems in the "PREPARE stmt" line. It seems to be excising most of the query string beyond a point, and the incomplete string leads to a syntax error. – Alptigin Jalayr Apr 04 '13 at 20:56
1

@AlptiginJalayr i think the problem is on the GROUP_CONCAT function, its maximum length is limited, but you can increase this limit using, for example, `SET SESSION group_concat_max_len = 1000000;` – fthiella Apr 04 '13 at 21:05
I did try that. However, it's not the group_concat which is going wrong, but the PREPARE stmt FROM [at]sql query. I know this because I did SELECT [at]sql before PREPARE, and it returned the complete query. – Alptigin Jalayr Apr 05 '13 at 14:24
@AlptiginJalayr i could not find a limit for a prepared query... if you do SELECT LENGTH(@sql) what length do you get? – fthiella Apr 06 '13 at 16:14
Sorry, I had to shut all that down over the weekend. I didn't get time to do the SELECT LENGTH you suggested. I'm going to go ahead and accept your answer, though, and add a small edit. Thanks a lot. – Alptigin Jalayr Apr 08 '13 at 13:18
@fthiella And thank you once again. As my table increased in size I suddenly got strange errors from the code. But like you pointed out it, the errors that were occurring were due to a limited size of the GROUP CONCAT. Adding the extra line solved that problem. – Ansjovis86 Mar 20 '17 at 17:27

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

1 Answers1

Linked