How to create table while using prepare from query in MySQL?

Question

I am new to SQL but needed a quick solution. I want to prepare a table from the query output below. But I am not sure where to insert the create table in the query:

SET SESSION group_concat_max_len = 92160;
SET @target_schema='rd';
SET @target_table='pbc_gl';
SET @target_where='`acct`';



select 
    GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')
    INTO @sql 
FROM (
    SELECT
        CONCAT(' SELECT `acct`,', QUOTE(COLUMN_NAME), ' AS `Business_Unit`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry
    FROM (
        SELECT `COLUMN_NAME` 
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA`=@target_schema 
            AND `TABLE_NAME`=@target_table
            AND COLUMN_NAME != 'acct'
    ) AS `A`
) AS `B` ;

PREPARE s FROM @sql;
EXECUTE s; 
DEALLOCATE PREPARE s;

Please help.

Lajos Arpad · Answer 1 · 2023-02-22T19:29:11.163

0

Your

select 
    GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')
    INTO @sql 
FROM (
    SELECT
        CONCAT(' SELECT `acct`,', QUOTE(COLUMN_NAME), ' AS `Business_Unit`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry
    FROM (
        SELECT `COLUMN_NAME` 
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA`=@target_schema 
            AND `TABLE_NAME`=@target_table
            AND COLUMN_NAME != 'acct'
    ) AS `A`
) AS `B` ;

Already assumes that your table already exists, since it loads information from information_schema.columns querying for your table, so, your table must exist before your EXECUTE s line. But, in my humble opinion, it would be great if you would run your create command above all the lines above, to make sure that the code is as understandable as possible.

EDIT

The problem discussed in the comment-section was this query:

SELECT CONCAT('CREATE TABLE ', QUOTE(final_results), ' AS ', GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')) INTO @sql 
FROM (
    SELECT CONCAT('SELECT acct,', QUOTE(COLUMN_NAME), ' AS Business_Unit,', COLUMN_NAME, ' AS value FROM ', @target_table, ' WHERE ', @target_where) qry 
    FROM ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@target_schema AND TABLE_NAME=@target_table AND COLUMN_NAME != 'acct' ) AS A
) AS B

You need to specify @target_table just after CREATE TABLE instead of a column name.

EDIT

The problem is in the highlighted part of the code:

Basically the QUOTE(final_results) in the SELECT clause specifies that the column (!!!) called final_results of the selection (specified by the FROM clause) needs to be evaluated and escaped into your SELECT clause and the value with this field will be the name of the new table. But such column does not exist in the relation you defined as B, given the fact that it only has a

CONCAT('SELECT `acct`,', QUOTE(COLUMN_NAME), ' AS `Business_Unit`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry

in the SELECT clause, so your inner query will have a result set that has a single column, called qry. You therefore tried to select final_results from a result-set which only has a qry field. Since the result set you are selecting from does not have a final_results field, you cannot refer to that either. As a result, you will need to decide for yourself what the table name should be and fix your query accordingly.

edited Feb 22 '23 at 19:29

answered Feb 21 '23 at 16:18

Lajos Arpad

64,414
37
100
175

Thank you for the update I am getting an error Error Code: 1054. Unknown column 'final_results' in 'field list' 0.015 sec I created my table at the beginning of the code drop table if exists final_results; create table final_results (acct varchar(50), Business_Unit varchar(50), value varchar(50)); – Siddhartha Feb 21 '23 at 18:15
@Siddhartha the code you have given should not cause that error. Can you point out the exact command that's errored out? It seems that somewhere you refer to a table's name as if it was a field name. – Lajos Arpad Feb 21 '23 at 18:26
Hi Lajos, the error is coming when it is running the select statement select GROUP_CONCAT(qry SEPARATOR ' UNION ALL ') INTO @sql – Siddhartha Feb 21 '23 at 18:43
SELECT CONCAT('CREATE TABLE ', QUOTE(final_results), ' AS ', GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')) INTO @sql FROM (SELECT CONCAT('SELECT `acct`,', QUOTE(COLUMN_NAME), ' AS `Business_Unit`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry FROM ( SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`=@target_schema AND `TABLE_NAME`=@target_table AND `COLUMN_NAME` != 'acct' ) AS `A` ) AS `B` Error Code: 1054. Unknown column 'final_results' in 'field list' 0.000 sec – Siddhartha Feb 21 '23 at 18:45
@Siddhartha the `QUOTE(final_results)` part is the problem, because at that level you only have a textual `qry` field – Lajos Arpad Feb 21 '23 at 18:49
@Siddhartha Edited my answer. If my answer helped you solve your problem, then you might consider accepting it as the correct answer. – Lajos Arpad Feb 21 '23 at 18:51
Hi, you want me to remove the 'qry' ? this is causing error in the query itself. did not get that point – Siddhartha Feb 21 '23 at 19:11
@Siddhartha no, the point I was making is that you have a `QUOTE(final_results)` in a context where `final_results` does not exist as a column. Instead of `QUOTE(final_results)` you will need to use a valid table name – Lajos Arpad Feb 21 '23 at 19:46
@Siddhartha It is also possible that I misunderstand your question. It would be great if you created a SQLFiddle where your problem could be reproduced and suggestions for solutions could be tested. – Lajos Arpad Feb 21 '23 at 19:49
I understand this is a bit back and forth but thanks till now. I had actually created the final_results table with the columns which are getting created. Also there is no column name final_results. – Siddhartha Feb 21 '23 at 20:01
@Siddhartha Somewhere `final_results` is assumed to be a column. That's what MySQL tells you in the error message. I would be happy to point out where did this happen, but I need more information. A SQLFiddle would help me provide you this information. Or your full script. – Lajos Arpad Feb 21 '23 at 20:07
Hi Lajos, so i tried SQLFiddle (again this was new to me) here is the link http://sqlfiddle.com/#!9/dbd19b/4 . It is showing the same error Unknown column 'final_results' in 'field list' . Could you please take a look.. A big thank you for this help – Siddhartha Feb 22 '23 at 17:57
@Siddhartha Thank you! I have edited my answer with further information. – Lajos Arpad Feb 22 '23 at 19:29
Ok, I got a rough idea of the explanation you have given. Thanks for that. I am trying to transpose the column name in pbc_gl to rows. The issue i have discussed in this link : https://stackoverflow.com/questions/75488978/how-to-pivot-columns-to-rows-in-mysql-for-n-number-of-columns-without-using-unio?noredirect=1#comment133200578_75488978 . So we only need to load the data in a table rather than just select statement. Any idea ? Like unless we can store the data in a table it will be pointless just to see the data as the number of columns are large. – Siddhartha Feb 23 '23 at 06:52
We are trying to transpose the data and store it in final_results table. – Siddhartha Feb 23 '23 at 06:54
@Siddhartha if you want to run a composite `select` and store the result into a table, you can do it with an `insert`-`select`, which roughly looks like `insert into targettable(c1, c2, ...) select c1, c2, ... from ...`. Basically, you already have a select statement. You only need to prepend an `insert into` just before the `select` as part of the same command, but you will need to make sure that the number, the order and the type of columns in the `insert` clause is compatible with your result set. See more here: https://www.w3schools.com/sql/sql_insert_into_select.asp – Lajos Arpad Feb 23 '23 at 10:19
You mean like this ? http://sqlfiddle.com/#!9/dbd19b/21 ... This is throwing error in MySQL – Siddhartha Feb 23 '23 at 14:04
@Siddhartha no. I tried to explain to you that `QUOTE(final_results)` will invariably fail unless you have a column by that name to refer to. I do not understand why you repeatedly refer to `QUOTE(final_results)`. It does not make sense to quote a table in a select clause. You will fail doing it, no matter how you try. So, you will either create a column by that name (which does not make sense) or figure out and explain what you wanted to achieve with QUOTE(final_results). – Lajos Arpad Feb 24 '23 at 12:39
@Siddhartha Look at http://sqlfiddle.com/#!9/554d0d/1. I have removed the `insert` clause to illustrate that your `select` will never properly run, because it cannot find `final_results` in the fields list. This is what I wanted to explain to you from the start of this discussion. – Lajos Arpad Feb 24 '23 at 12:42
How would you suggest we get store the results in another table ? for this code – Siddhartha Feb 27 '23 at 08:34
@Siddhartha a popular technique for that would be the following: 1. Make sure you have a `SELECT` statement that works; 2. Take a look at the columns of the `SELECT` statement and compare them in number and type to the result of `DESC ;`; 3. Prepend an `INSERT INTO (...)` to your select statement. That converts your `SELECT` into an `INSERT-SELECT` statement, that is, what formerly was a read query was transferred into a read & write operation. – Lajos Arpad Feb 27 '23 at 12:05
@Siddhartha read more here about insert-select statements: https://www.w3schools.com/sql/sql_insert_into_select.asp. You will need to make sure that 1. All the fields in your target table that are not nullable and have no default value either are present into the into(...) part; 2. The number and types of the into fields are matching the number and types of the select clause. – Lajos Arpad Feb 27 '23 at 12:07

How to create table while using prepare from query in MySQL?

1 Answers1