-1

i need to create a temporary table which column name will generate dynamically using a select command (mean: the value of another table.). But when i run the command then the temp table created with the value not the column name. i have tried as below:

SELECT ColVal FROM tableA

TableA

------------------------
ColName | ColVal 
------------------------
id      | 1
----------------------
Name    | Test
----------------------
Age     |25
---------------------

Now , i need to create Temp table B which column name will be as below

TableB

--------------
id| Name| Age|
--------------

I have tried by the query:

CREATE TEMPORARY TABLE  TableB as (select ColName  from TableA)

it's not give me the actual output. it create the table like:

TableB

-----------------
ColName
-----------------
id
--------
Name
--------
Age
--------
riad
  • 7,144
  • 22
  • 59
  • 70
  • You can try pivoting on the `id` column to transform rows into columns. Just Google "pivot query SQL" and you will find help. – Tim Biegeleisen Mar 06 '18 at 10:01
  • May be duplicate: https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table – Suresh Kamrushi Mar 06 '18 at 10:01
  • problem is . the field is dynamic, mean: in here i write only 3 value. it could be 3 to 30 different name. – riad Mar 06 '18 at 10:09

1 Answers1

0

I'm not sure that it is the best way to do it, but you can run dynamically constructed sql. You can do something like that:

SELECT  CONCAT("CREATE TEMPORARY TABLE TableB (", 
               group_concat( CONCAT(ColName, " VARCHAR(50)")), ");")
               INTO @Expression
FROM TableA;

PREPARE myquery FROM @Expression;
EXECUTE myquery;

@Expression will be equal to: CREATE TEMPORARY TABLE TableB (id VARCHAR(50),Name VARCHAR(50),Age VARCHAR(50));

AlbertK
  • 11,841
  • 5
  • 40
  • 36
  • It might be useful include a distinct clause in the group_concat in case of repeating tokens ie group_concat(distinct – P.Salmon Mar 06 '18 at 10:58