How can I create a table from an existing table in SQL but using cells from the old table as columns in the new table?

Question

How can I create a table from an existing table in SQL but using cells from the old table as columns in the new table?

PlatinumMaths

2021年4月19日 23:13

I have a table,

and I want to create a new table such as the one below (from the table above)

In SQL, I tried using the following commands. I am able to generate a table with only one column like this,

CREATE TABLE table2 AS
SELECT balance
FROM table1 WHERE balance='currency'

But if I try to do multiple WHERE clause's it doesn't seem to work.

I tried to do,

CREATE TABLE table2 AS
SELECT balance, category
FROM table1 WHERE balance='currency', category= 'date_valid_from'

I also tried using an AND statement but still no luck,

CREATE TABLE table1 AS
SELECT balance, category
FROM table2 WHERE balance='currency' AND category = 'date_valid_from'

This just returns empty columns of 'balance' and 'category' and not the specified Where clauses.

ALSO, the columns in the new tables, are the ones which I selected from the old table. i.e 'balance' and 'category' However I wan't the cells within those columns i.e 'currency' in 'balance' to be the new columns.

Topic sql databases

Category Data Science

Nitin · Accepted Answer · 2021年4月19日 23:13

CREATE newtable AS SELECT * from oldtable.

Then delete the data that you don't want in newtable with the WHERE clauses.

Then you're good to go.

By the way this is not a data science question, please go to programming stackexchange.

CoveredInChocolate · Accepted Answer · 2021年4月18日 18:49

I tried this once, but due to some database settings I was not permitted to create a table with an SQL-command. Maybe something similar is happening to you? I had to copy the DDL code (Data Definition Language) for the table and create it, and then add the rows.

Maybe you could first copy and run the DDL. Should be available in whatever program you are using for the database.
https://www.educba.com/sql-ddl-commands/

Once the table is created, it will be empty. Then you populate it. Here is some example code.

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition1 AND condition2 AND condition3;

How can I create a table from an existing table in SQL but using cells from the old table as columns in the new table?

About