1

I have a joint table that gives me a list of IDs and some verbs as:

id  | name
------------
23  | sell
123 | steal
25  | assault
....

I need to end up with a new table that will have an auto-increment column, the IDs column and as other columns all the verbs in the name column. Similar to:

id   | sell |  steal | assault
-------------------------------
23   | 
123  |
...

I know how to create a table and have an auto-increment ID or get the IDs in the table but don't know how to make fields from a joint table(or any table) as columns. Is there a way to do this in one SQL statement? Either that or using Java ?

techiemouse
  • 11
  • 5
  • You could try to pivot the result and then use a SELECT INTO... – ericpap Apr 10 '14 at 12:32
  • See answer here: http://stackoverflow.com/questions/15820082/create-a-table-with-column-names-derived-from-row-values-of-another-table – Cyril Gips Apr 10 '14 at 12:33
  • @CyrilGips - Yes, but on that other question, the OP has stated that the only answer doesn't work. – Dawood ibn Kareem Apr 10 '14 at 12:36
  • Yeah, I'd run a `SELECT DISTINCT` through Java (or other programming language of your choice). Then, still within the Java, paste the results together into a `CREATE TABLE` command and run that. – Dawood ibn Kareem Apr 10 '14 at 12:39

2 Answers2

0

You can make a view with two table and you can edit on this view 

Exp 

Create View V_New as 
select * from table1,table2 and table1.ID= table2.ID
Ahmed Galal
  • 694
  • 7
  • 21
0

Thank everyone for the tips. I ended up doing it a bit different: Got all verbs with a SELECT statement in an array list. Created a String using StringBuilder with the statements needed to create a table and appended to that each element of the array list:

stringBuilder.append("CREATE TABLE IF NOT EXISTS tablename ( id INT NOT NULL AUTO_INCREMENT, article_id INT, ");
for (int i=0; i<verbList.size(); i++){
    stringBuilder.append("`"+verbList.get(i)+"`"+" SMALLINT, ");
}
stringBuilder.append("PRIMARY KEY (id))");
String finalString = stringBuilder.toString();

Then just executed it as a prepared statement.

techiemouse
  • 11
  • 5