Add column with name of variable value

Question

Inside procedure, I want to create a temporary table "report" with column names of another table "descriptions" rows contents, but I get error, because my query instead of using variable "tmp_description" value, uses its name to create a new column. How to use variable value as name for the new column?

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE tmp_description varchar(30);

...

CREATE TEMPORARY TABLE descriptions (description varchar(30));

insert into descriptions
        select distinct description from pure;
    
SELECT COUNT(*) INTO n FROM descriptions;
            SET i=0;
            WHILE i<n DO 
                SELECT * INTO tmp_description FROM (SELECT * FROM descriptions LIMIT i,1) t1;
                ALTER TABLE report
                ADD COLUMN 
                    tmp_description FLOAT(2) DEFAULT 0.0;          <-- I get error here
                SET i = i + 1;
            END WHILE;

@MuhKandaWibawaPutra Another DBMS, won't help (except the fact that dynamic SQL needed). — Akina
– Akina, Commented Apr 1, 2021 at 10:44

Gordon Linoff · Accepted Answer · 2021-04-01 10:48:16Z

1

I don't see any value to doing this in a while loop. Your looping mechanism is all off anyway, because you are using LIMIT without ORDER BY -- which means that the row returned on each iteration is arbitrary.

Why not just construct a single statement? First run:

select group_concat('add column ', description, ' numeric(2)' separator ', ') as columns
from t;

Note that float(2) doesn't really make sense to me as a data type. I suspect that you really want a numeric/decimal type.

Then take the results. Prepend them with alter table report and run the code.

You could do this using dynamic SQL, but I see no advantage to doing that.

answered Apr 1, 2021 at 10:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add column with name of variable value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related